[Home] [Help]
189: l_global_count number :=0;
190:
191: BEGIN
192:
193: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Headers()+' );
194:
195: IF (process_mode = 'LOCK') THEN
196:
197: -- try to open table for update, if it fails the exception will
196:
197: -- try to open table for update, if it fails the exception will
198: -- tell us that the merge was going to block
199:
200: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
201: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADERS_ALL', FALSE );
202:
203: open merge_headers;
204: close merge_headers;
197: -- try to open table for update, if it fails the exception will
198: -- tell us that the merge was going to block
199:
200: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
201: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADERS_ALL', FALSE );
202:
203: open merge_headers;
204: close merge_headers;
205:
248: old_hdr_end_cust_id_tab := hdr_end_cust_id_tab;
249:
250:
251: IF l_dbi_profile = 'Y' then
252: arp_message.set_line(' update_date_tab_count for hdr='||dbi_local_hdr_update_date_tab.COUNT);
253: IF dbi_local_hdr_update_date_tab.COUNT > 0 then
254:
255:
256: IF dbi_header_update_date_tab.COUNT > 0 then
307: end if;
308:
309: end loop;
310: end if;
311: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Headers():3' );
312: --insert audit information for customer merge
313: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
314: IF hdr_header_id_tab.COUNT <> 0 THEN
315: forall i in hdr_header_id_tab.FIRST..hdr_header_id_tab.LAST
370: end if;
371: end if;
372:
373:
374: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
375: arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
376: IF hdr_header_id_tab.COUNT <> 0 THEN
377:
378: FORALL i in hdr_header_id_tab.FIRST..hdr_header_id_tab.LAST
371: end if;
372:
373:
374: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
375: arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
376: IF hdr_header_id_tab.COUNT <> 0 THEN
377:
378: FORALL i in hdr_header_id_tab.FIRST..hdr_header_id_tab.LAST
379: UPDATE OE_ORDER_HEADERS_ALL H
398: g_count := sql%rowcount;
399: ELSE
400: g_count := 0;
401: END IF;
402: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
403: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
404: EXIT WHEN merge_headers_2%NOTFOUND;
405:
406: hdr_header_id_tab.DELETE;
399: ELSE
400: g_count := 0;
401: END IF;
402: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
403: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
404: EXIT WHEN merge_headers_2%NOTFOUND;
405:
406: hdr_header_id_tab.DELETE;
407: hdr_sold_to_org_id_tab.DELETE;
428: customer_site_id_tab.DELETE;
429: dbi_local_hdr_header_id_tab.DELETE;
430: dbi_local_hdr_update_date_tab.DELETE;
431:
432: arp_message.set_line(' header_id_tab_count for hdr='||dbi_header_header_id_tab.COUNT);
433: arp_message.set_line(' update_date_tab_count for hdr='||dbi_header_update_date_tab.COUNT);
434:
435: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Headers()-' );
436:
429: dbi_local_hdr_header_id_tab.DELETE;
430: dbi_local_hdr_update_date_tab.DELETE;
431:
432: arp_message.set_line(' header_id_tab_count for hdr='||dbi_header_header_id_tab.COUNT);
433: arp_message.set_line(' update_date_tab_count for hdr='||dbi_header_update_date_tab.COUNT);
434:
435: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Headers()-' );
436:
437: EXCEPTION
431:
432: arp_message.set_line(' header_id_tab_count for hdr='||dbi_header_header_id_tab.COUNT);
433: arp_message.set_line(' update_date_tab_count for hdr='||dbi_header_update_date_tab.COUNT);
434:
435: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Headers()-' );
436:
437: EXCEPTION
438: when others then
439: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Headers-' );
435: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Headers()-' );
436:
437: EXCEPTION
438: when others then
439: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Headers-' );
440: raise;
441:
442: END OE_Merge_Headers;
443:
611: customer_site_id_tab num_table_binary_int;
612:
613: BEGIN
614:
615: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_HEADER_History()+' );
616:
617: IF (process_mode = 'LOCK') THEN
618:
619: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
615: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_HEADER_History()+' );
616:
617: IF (process_mode = 'LOCK') THEN
618:
619: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
620: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
621:
622: open merge_headers_history;
623: close merge_headers_history;
616:
617: IF (process_mode = 'LOCK') THEN
618:
619: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
620: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
621:
622: open merge_headers_history;
623: close merge_headers_history;
624:
688: end loop;
689: end if;
690:
691:
692: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
693: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
694:
695: IF hhst_header_id_tab.COUNT <> 0 THEN
696: FORALL i in hhst_header_id_tab.FIRST..hhst_header_id_tab.LAST
689: end if;
690:
691:
692: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
693: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
694:
695: IF hhst_header_id_tab.COUNT <> 0 THEN
696: FORALL i in hhst_header_id_tab.FIRST..hhst_header_id_tab.LAST
697: UPDATE OE_ORDER_HEADER_HISTORY HHIST
716: ELSE
717: g_count := 0;
718: END IF;
719:
720: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
721: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
722: EXIT WHEN merge_headers_history_2%NOTFOUND;
723:
724: hhst_header_id_tab.DELETE;
717: g_count := 0;
718: END IF;
719:
720: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
721: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
722: EXIT WHEN merge_headers_history_2%NOTFOUND;
723:
724: hhst_header_id_tab.DELETE;
725: hhst_sold_to_org_id_tab.DELETE;
736:
737: END IF;
738: customer_id_tab.DELETE;
739: customer_site_id_tab.DELETE;
740: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_History()-' );
741:
742: EXCEPTION
743: when others then
744: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Header_History-' );
740: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_History()-' );
741:
742: EXCEPTION
743: when others then
744: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Header_History-' );
745: raise;
746:
747: END OE_MERGE_HEADER_HISTORY; -- 3196900
748:
826: and m.set_number = set_num)
827: for update nowait;
828:
829: BEGIN
830: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_History()+' );
831:
832: -- both customer and site level
833:
834: IF( process_mode = 'LOCK' ) THEN
832: -- both customer and site level
833:
834: IF( process_mode = 'LOCK' ) THEN
835:
836: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
837: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
838:
839: open c1;
840: close c1;
833:
834: IF( process_mode = 'LOCK' ) THEN
835:
836: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
837: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
838:
839: open c1;
840: close c1;
841:
860: ELSE
861:
862:
863: -- site level update
864: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
865: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
866:
867: UPDATE oe_order_header_history a
868: set ship_to_org_id = (select distinct m.customer_site_id
861:
862:
863: -- site level update
864: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
865: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
866:
867: UPDATE oe_order_header_history a
868: set ship_to_org_id = (select distinct m.customer_site_id
869: from ra_customer_merges m
883: where m.process_flag = 'N'
884: and m.request_id = req_id
885: and m.set_number = set_num);
886: g_count := sql%rowcount;
887: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
888: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
889:
890:
891: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
884: and m.request_id = req_id
885: and m.set_number = set_num);
886: g_count := sql%rowcount;
887: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
888: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
889:
890:
891: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
892: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
887: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
888: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
889:
890:
891: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
892: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
893:
894: UPDATE oe_order_header_history a
895: set invoice_to_org_id = (select distinct m.customer_site_id
888: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
889:
890:
891: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
892: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
893:
894: UPDATE oe_order_header_history a
895: set invoice_to_org_id = (select distinct m.customer_site_id
896: from ra_customer_merges m
910: where m.process_flag = 'N'
911: and m.request_id = req_id
912: and m.set_number = set_num);
913: g_count := sql%rowcount;
914: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
915: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
916:
917: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
918: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
911: and m.request_id = req_id
912: and m.set_number = set_num);
913: g_count := sql%rowcount;
914: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
915: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
916:
917: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
918: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
919:
913: g_count := sql%rowcount;
914: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
915: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
916:
917: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
918: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
919:
920: UPDATE oe_order_header_history a
921: set deliver_to_org_id = (select distinct m.customer_site_id
914: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
915: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
916:
917: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
918: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
919:
920: UPDATE oe_order_header_history a
921: set deliver_to_org_id = (select distinct m.customer_site_id
922: from ra_customer_merges m
936: where m.process_flag = 'N'
937: and m.request_id = req_id
938: and m.set_number = set_num);
939: g_count := sql%rowcount;
940: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
941: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
942:
943: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
944: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
937: and m.request_id = req_id
938: and m.set_number = set_num);
939: g_count := sql%rowcount;
940: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
941: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
942:
943: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
944: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
945:
939: g_count := sql%rowcount;
940: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
941: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
942:
943: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
944: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
945:
946:
947: -- customer level update --
940: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
941: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
942:
943: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
944: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
945:
946:
947: -- customer level update --
948:
945:
946:
947: -- customer level update --
948:
949: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
950: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
951:
952: UPDATE oe_order_header_history a
953: set sold_to_org_id = (select distinct m.customer_id
946:
947: -- customer level update --
948:
949: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
950: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
951:
952: UPDATE oe_order_header_history a
953: set sold_to_org_id = (select distinct m.customer_id
954: from ra_customer_merges m
970: and m.set_number = set_num);
971:
972: g_count := sql%rowcount;
973:
974: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
975: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
976:
977: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
978: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
971:
972: g_count := sql%rowcount;
973:
974: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
975: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
976:
977: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
978: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
979:
973:
974: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
975: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
976:
977: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
978: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
979:
980: UPDATE oe_order_header_history a
981: set sold_to_site_use_id = (select distinct m.customer_id
974: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
975: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
976:
977: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
978: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
979:
980: UPDATE oe_order_header_history a
981: set sold_to_site_use_id = (select distinct m.customer_id
982: from ra_customer_merges m
998: and m.set_number = set_num);
999:
1000: g_count := sql%rowcount;
1001:
1002: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1003: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1004:
1005: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1006: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
999:
1000: g_count := sql%rowcount;
1001:
1002: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1003: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1004:
1005: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1006: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1007:
1001:
1002: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1003: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1004:
1005: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1006: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1007:
1008: UPDATE oe_order_header_history a
1009: set end_customer_site_use_id = (select distinct m.customer_id
1002: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1003: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1004:
1005: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1006: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1007:
1008: UPDATE oe_order_header_history a
1009: set end_customer_site_use_id = (select distinct m.customer_id
1010: from ra_customer_merges m
1026: and m.set_number = set_num);
1027:
1028: g_count := sql%rowcount;
1029:
1030: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1031: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1032:
1033: -- customer level update --
1034:
1027:
1028: g_count := sql%rowcount;
1029:
1030: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1031: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1032:
1033: -- customer level update --
1034:
1035: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1031: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1032:
1033: -- customer level update --
1034:
1035: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1036: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
1037:
1038: UPDATE oe_order_header_history a
1039: set end_customer_id = (select distinct m.customer_id
1032:
1033: -- customer level update --
1034:
1035: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1036: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
1037:
1038: UPDATE oe_order_header_history a
1039: set end_customer_id = (select distinct m.customer_id
1040: from ra_customer_merges m
1056: and m.set_number = set_num);
1057:
1058: g_count := sql%rowcount;
1059:
1060: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1061: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1062:
1063: END IF;
1064:
1057:
1058: g_count := sql%rowcount;
1059:
1060: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1061: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1062:
1063: END IF;
1064:
1065: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_History()-' );
1061: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1062:
1063: END IF;
1064:
1065: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_History()-' );
1066:
1067: EXCEPTION
1068: When others then
1069: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Header_History-' );
1065: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_History()-' );
1066:
1067: EXCEPTION
1068: When others then
1069: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Header_History-' );
1070: raise;
1071:
1072: END OE_Merge_Header_History; */
1073:
1131:
1132:
1133:
1134: BEGIN
1135: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Headers_IFACE()+' );
1136:
1137: -- both customer and site level
1138:
1139: IF( process_mode = 'LOCK' ) THEN
1137: -- both customer and site level
1138:
1139: IF( process_mode = 'LOCK' ) THEN
1140:
1141: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1142: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1143:
1144: open c1;
1145: close c1;
1138:
1139: IF( process_mode = 'LOCK' ) THEN
1140:
1141: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1142: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1143:
1144: open c1;
1145: close c1;
1146:
1156:
1157: ELSE
1158:
1159: -- site level update
1160: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1161: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1162:
1163: UPDATE oe_headers_iface_all a
1164: set ship_to_org_id = (select distinct m.customer_site_id
1157: ELSE
1158:
1159: -- site level update
1160: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1161: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1162:
1163: UPDATE oe_headers_iface_all a
1164: set ship_to_org_id = (select distinct m.customer_site_id
1165: from ra_customer_merges m
1179: where m.process_flag = 'N'
1180: and m.request_id = req_id
1181: and m.set_number = set_num);
1182: g_count := sql%rowcount;
1183: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1184: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1185:
1186: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1187: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1180: and m.request_id = req_id
1181: and m.set_number = set_num);
1182: g_count := sql%rowcount;
1183: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1184: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1185:
1186: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1187: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1188:
1182: g_count := sql%rowcount;
1183: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1184: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1185:
1186: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1187: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1188:
1189: UPDATE oe_headers_iface_all a
1190: set invoice_to_org_id = (select distinct m.customer_site_id
1183: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1184: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1185:
1186: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1187: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1188:
1189: UPDATE oe_headers_iface_all a
1190: set invoice_to_org_id = (select distinct m.customer_site_id
1191: from ra_customer_merges m
1205: where m.process_flag = 'N'
1206: and m.request_id = req_id
1207: and m.set_number = set_num);
1208: g_count := sql%rowcount;
1209: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1210: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1211:
1212: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1213: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1206: and m.request_id = req_id
1207: and m.set_number = set_num);
1208: g_count := sql%rowcount;
1209: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1210: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1211:
1212: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1213: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1214:
1208: g_count := sql%rowcount;
1209: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1210: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1211:
1212: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1213: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1214:
1215: UPDATE oe_headers_iface_all a
1216: set deliver_to_org_id = (select distinct m.customer_site_id
1209: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1210: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1211:
1212: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1213: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1214:
1215: UPDATE oe_headers_iface_all a
1216: set deliver_to_org_id = (select distinct m.customer_site_id
1217: from ra_customer_merges m
1231: where m.process_flag = 'N'
1232: and m.request_id = req_id
1233: and m.set_number = set_num);
1234: g_count := sql%rowcount;
1235: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1236: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1237:
1238: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1239: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1232: and m.request_id = req_id
1233: and m.set_number = set_num);
1234: g_count := sql%rowcount;
1235: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1236: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1237:
1238: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1239: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1240:
1234: g_count := sql%rowcount;
1235: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1236: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1237:
1238: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1239: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1240:
1241:
1242: -- customer level update
1235: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1236: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1237:
1238: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1239: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1240:
1241:
1242: -- customer level update
1243: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1239: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1240:
1241:
1242: -- customer level update
1243: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1244: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1245:
1246: UPDATE oe_headers_iface_all a
1247: set sold_to_org_id = (select distinct m.customer_id
1240:
1241:
1242: -- customer level update
1243: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1244: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1245:
1246: UPDATE oe_headers_iface_all a
1247: set sold_to_org_id = (select distinct m.customer_id
1248: from ra_customer_merges m
1264: and m.set_number = set_num);
1265:
1266: g_count := sql%rowcount;
1267:
1268: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1269: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1270:
1271: END IF;
1272:
1265:
1266: g_count := sql%rowcount;
1267:
1268: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1269: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1270:
1271: END IF;
1272:
1273: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Headers_IFACE()-' );
1269: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1270:
1271: END IF;
1272:
1273: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Headers_IFACE()-' );
1274:
1275: EXCEPTION
1276: When others then
1277: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Headers_IFACE-' );
1273: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Headers_IFACE()-' );
1274:
1275: EXCEPTION
1276: When others then
1277: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Headers_IFACE-' );
1278: raise;
1279:
1280: END OE_Merge_Headers_IFACE;
1281:
1384:
1385: l_profile_val VARCHAR2(30);
1386:
1387: BEGIN
1388: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_ACKS()+' );
1389:
1390: /* both customer and site level */
1391:
1392: IF( process_mode = 'LOCK' ) THEN
1390: /* both customer and site level */
1391:
1392: IF( process_mode = 'LOCK' ) THEN
1393:
1394: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1395: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1396:
1397: open c1;
1398: close c1;
1391:
1392: IF( process_mode = 'LOCK' ) THEN
1393:
1394: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1395: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1396:
1397: open c1;
1398: close c1;
1399:
1459: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1460: end if;
1461:
1462: /* site level update */
1463: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1464: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1465:
1466: UPDATE OE_HEADER_ACKS a
1467: set ship_to_org_id = (select distinct m.customer_site_id
1460: end if;
1461:
1462: /* site level update */
1463: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1464: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1465:
1466: UPDATE OE_HEADER_ACKS a
1467: set ship_to_org_id = (select distinct m.customer_site_id
1468: from ra_customer_merges m
1483: and m.request_id = req_id
1484: and m.set_number = set_num)
1485: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1486: g_count := sql%rowcount;
1487: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1488: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1489:
1490: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1491: insert into HZ_CUSTOMER_MERGE_LOG (
1484: and m.set_number = set_num)
1485: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1486: g_count := sql%rowcount;
1487: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1488: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1489:
1490: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1491: insert into HZ_CUSTOMER_MERGE_LOG (
1492: MERGE_LOG_ID,
1525: and m.set_number = set_num
1526: and NVL(h.ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1527: end if;
1528:
1529: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1530: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1531:
1532: UPDATE OE_HEADER_ACKS a
1533: set invoice_to_org_id = (select distinct m.customer_site_id
1526: and NVL(h.ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1527: end if;
1528:
1529: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1530: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1531:
1532: UPDATE OE_HEADER_ACKS a
1533: set invoice_to_org_id = (select distinct m.customer_site_id
1534: from ra_customer_merges m
1549: and m.request_id = req_id
1550: and m.set_number = set_num)
1551: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1552: g_count := sql%rowcount;
1553: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1554: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1555:
1556: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1557: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1550: and m.set_number = set_num)
1551: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1552: g_count := sql%rowcount;
1553: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1554: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1555:
1556: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1557: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1558:
1552: g_count := sql%rowcount;
1553: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1554: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1555:
1556: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1557: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1558:
1559: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1560: insert into HZ_CUSTOMER_MERGE_LOG (
1553: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1554: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1555:
1556: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1557: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1558:
1559: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1560: insert into HZ_CUSTOMER_MERGE_LOG (
1561: MERGE_LOG_ID,
1615: and m.request_id = req_id
1616: and m.set_number = set_num)
1617: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1618: g_count := sql%rowcount;
1619: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1620: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1621:
1622: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1623: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1616: and m.set_number = set_num)
1617: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1618: g_count := sql%rowcount;
1619: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1620: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1621:
1622: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1623: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1624:
1618: g_count := sql%rowcount;
1619: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1620: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1621:
1622: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1623: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1624:
1625:
1626: /* customer level update */
1619: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1620: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1621:
1622: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1623: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1624:
1625:
1626: /* customer level update */
1627: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1623: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1624:
1625:
1626: /* customer level update */
1627: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1628: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1629:
1630: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1631: insert into HZ_CUSTOMER_MERGE_LOG (
1624:
1625:
1626: /* customer level update */
1627: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1628: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1629:
1630: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1631: insert into HZ_CUSTOMER_MERGE_LOG (
1632: MERGE_LOG_ID,
1690: and a.header_id in (select header_id from oe_order_headers);
1691:
1692: g_count := sql%rowcount;
1693:
1694: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1695: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1696:
1697: -- sold_to_site_use_id merge
1698:
1691:
1692: g_count := sql%rowcount;
1693:
1694: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1695: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1696:
1697: -- sold_to_site_use_id merge
1698:
1699: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1695: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1696:
1697: -- sold_to_site_use_id merge
1698:
1699: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1700: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1701:
1702: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1703: insert into HZ_CUSTOMER_MERGE_LOG (
1696:
1697: -- sold_to_site_use_id merge
1698:
1699: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1700: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1701:
1702: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1703: insert into HZ_CUSTOMER_MERGE_LOG (
1704: MERGE_LOG_ID,
1762: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1763:
1764: g_count := sql%rowcount;
1765:
1766: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1767: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1768:
1769:
1770: -- end_customer_site_use_id merge
1763:
1764: g_count := sql%rowcount;
1765:
1766: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1767: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1768:
1769:
1770: -- end_customer_site_use_id merge
1771:
1768:
1769:
1770: -- end_customer_site_use_id merge
1771:
1772: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1773: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1774:
1775: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1776: insert into HZ_CUSTOMER_MERGE_LOG (
1769:
1770: -- end_customer_site_use_id merge
1771:
1772: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1773: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1774:
1775: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1776: insert into HZ_CUSTOMER_MERGE_LOG (
1777: MERGE_LOG_ID,
1837: NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1838:
1839: g_count := sql%rowcount;
1840:
1841: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1842: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1843:
1844: /* customer level update */
1845: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1838:
1839: g_count := sql%rowcount;
1840:
1841: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1842: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1843:
1844: /* customer level update */
1845: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1846: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1841: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1842: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1843:
1844: /* customer level update */
1845: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1846: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1847:
1848: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1849: insert into HZ_CUSTOMER_MERGE_LOG (
1842: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1843:
1844: /* customer level update */
1845: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1846: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1847:
1848: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
1849: insert into HZ_CUSTOMER_MERGE_LOG (
1850: MERGE_LOG_ID,
1910: and a.header_id in (select header_id from oe_order_headers);
1911:
1912: g_count := sql%rowcount;
1913:
1914: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1915: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1916:
1917: END IF;
1918:
1911:
1912: g_count := sql%rowcount;
1913:
1914: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1915: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1916:
1917: END IF;
1918:
1919: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_ACKS()-' );
1915: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1916:
1917: END IF;
1918:
1919: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_ACKS()-' );
1920:
1921: EXCEPTION
1922: When others then
1923: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Header_ACKS-' );
1919: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Header_ACKS()-' );
1920:
1921: EXCEPTION
1922: When others then
1923: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Header_ACKS-' );
1924: raise;
1925:
1926: END OE_Merge_Header_ACKS;
1927:
2152: l_global_count number :=0;
2153:
2154: BEGIN
2155:
2156: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines()+' );
2157:
2158: IF (process_mode = 'LOCK') THEN
2159:
2160: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2156: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines()+' );
2157:
2158: IF (process_mode = 'LOCK') THEN
2159:
2160: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2161: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
2162:
2163: open merge_lines;
2164: close merge_lines;
2157:
2158: IF (process_mode = 'LOCK') THEN
2159:
2160: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2161: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
2162:
2163: open merge_lines;
2164: close merge_lines;
2165:
2180:
2181: HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
2182: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
2183:
2184: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines():1' );
2185:
2186: OPEN merge_lines_2;
2187: LOOP
2188: FETCH merge_lines_2
2201: MERGE_HEADER_ID_LIST
2202: LIMIT 20000;
2203:
2204:
2205: arp_message.set_line('local line_hdr_id_count='||dbi_local_line_header_id_tab.COUNT);
2206: arp_message.set_line('local line_line__id_count='||dbi_local_line_line_id_tab.COUNT);
2207: arp_message.set_line('local line_date_count='||dbi_local_line_update_date_tab.COUNT);
2208: arp_message.set_line('local line_status_count='||dbi_local_line_status_tab.COUNT);
2209: IF l_dbi_profile = 'Y' then
2202: LIMIT 20000;
2203:
2204:
2205: arp_message.set_line('local line_hdr_id_count='||dbi_local_line_header_id_tab.COUNT);
2206: arp_message.set_line('local line_line__id_count='||dbi_local_line_line_id_tab.COUNT);
2207: arp_message.set_line('local line_date_count='||dbi_local_line_update_date_tab.COUNT);
2208: arp_message.set_line('local line_status_count='||dbi_local_line_status_tab.COUNT);
2209: IF l_dbi_profile = 'Y' then
2210: IF dbi_local_line_update_date_tab.COUNT > 0 then
2203:
2204:
2205: arp_message.set_line('local line_hdr_id_count='||dbi_local_line_header_id_tab.COUNT);
2206: arp_message.set_line('local line_line__id_count='||dbi_local_line_line_id_tab.COUNT);
2207: arp_message.set_line('local line_date_count='||dbi_local_line_update_date_tab.COUNT);
2208: arp_message.set_line('local line_status_count='||dbi_local_line_status_tab.COUNT);
2209: IF l_dbi_profile = 'Y' then
2210: IF dbi_local_line_update_date_tab.COUNT > 0 then
2211:
2204:
2205: arp_message.set_line('local line_hdr_id_count='||dbi_local_line_header_id_tab.COUNT);
2206: arp_message.set_line('local line_line__id_count='||dbi_local_line_line_id_tab.COUNT);
2207: arp_message.set_line('local line_date_count='||dbi_local_line_update_date_tab.COUNT);
2208: arp_message.set_line('local line_status_count='||dbi_local_line_status_tab.COUNT);
2209: IF l_dbi_profile = 'Y' then
2210: IF dbi_local_line_update_date_tab.COUNT > 0 then
2211:
2212:
2332:
2333: end if;
2334: end if;
2335:
2336: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2337: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
2338: IF line_line_id_tab.COUNT <> 0 THEN
2339: FORALL i in line_line_id_tab.FIRST..line_line_id_tab.LAST
2340: UPDATE OE_ORDER_LINES_ALL L
2333: end if;
2334: end if;
2335:
2336: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2337: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
2338: IF line_line_id_tab.COUNT <> 0 THEN
2339: FORALL i in line_line_id_tab.FIRST..line_line_id_tab.LAST
2340: UPDATE OE_ORDER_LINES_ALL L
2341: SET sold_to_org_id = decode(line_sold_to_org_id_tab(i),null,sold_to_org_id,line_sold_to_org_id_tab(i)),
2360:
2361: ELSE
2362: g_count := 0;
2363: END IF;
2364: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2365: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2366: EXIT WHEN merge_lines_2%NOTFOUND;
2367: line_line_id_tab.DELETE;
2368: line_sold_to_org_id_tab.DELETE;
2361: ELSE
2362: g_count := 0;
2363: END IF;
2364: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2365: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2366: EXIT WHEN merge_lines_2%NOTFOUND;
2367: line_line_id_tab.DELETE;
2368: line_sold_to_org_id_tab.DELETE;
2369: line_invoice_to_org_id_tab.DELETE;
2391: dbi_local_line_update_date_tab.DELETE;
2392: dbi_local_line_line_id_tab.DELETE;
2393: dbi_local_line_status_tab.DELETE;
2394:
2395: arp_message.set_line(' line_hdr_id_count='||dbi_line_header_id_tab.COUNT);
2396: arp_message.set_line(' line_line__id_count='||dbi_line_line_id_tab.COUNT);
2397: arp_message.set_line(' line_date_count='||dbi_line_update_date_tab.COUNT);
2398: arp_message.set_line(' line_status_count='||dbi_line_status_tab.COUNT);
2399: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Lines()-' );
2392: dbi_local_line_line_id_tab.DELETE;
2393: dbi_local_line_status_tab.DELETE;
2394:
2395: arp_message.set_line(' line_hdr_id_count='||dbi_line_header_id_tab.COUNT);
2396: arp_message.set_line(' line_line__id_count='||dbi_line_line_id_tab.COUNT);
2397: arp_message.set_line(' line_date_count='||dbi_line_update_date_tab.COUNT);
2398: arp_message.set_line(' line_status_count='||dbi_line_status_tab.COUNT);
2399: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Lines()-' );
2400:
2393: dbi_local_line_status_tab.DELETE;
2394:
2395: arp_message.set_line(' line_hdr_id_count='||dbi_line_header_id_tab.COUNT);
2396: arp_message.set_line(' line_line__id_count='||dbi_line_line_id_tab.COUNT);
2397: arp_message.set_line(' line_date_count='||dbi_line_update_date_tab.COUNT);
2398: arp_message.set_line(' line_status_count='||dbi_line_status_tab.COUNT);
2399: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Lines()-' );
2400:
2401: EXCEPTION
2394:
2395: arp_message.set_line(' line_hdr_id_count='||dbi_line_header_id_tab.COUNT);
2396: arp_message.set_line(' line_line__id_count='||dbi_line_line_id_tab.COUNT);
2397: arp_message.set_line(' line_date_count='||dbi_line_update_date_tab.COUNT);
2398: arp_message.set_line(' line_status_count='||dbi_line_status_tab.COUNT);
2399: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Lines()-' );
2400:
2401: EXCEPTION
2402: when others then
2395: arp_message.set_line(' line_hdr_id_count='||dbi_line_header_id_tab.COUNT);
2396: arp_message.set_line(' line_line__id_count='||dbi_line_line_id_tab.COUNT);
2397: arp_message.set_line(' line_date_count='||dbi_line_update_date_tab.COUNT);
2398: arp_message.set_line(' line_status_count='||dbi_line_status_tab.COUNT);
2399: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Lines()-' );
2400:
2401: EXCEPTION
2402: when others then
2403: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Lines-' );
2399: arp_message.set_line( ' END OE_CUST_MERGE.OE_Merge_Lines()-' );
2400:
2401: EXCEPTION
2402: when others then
2403: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Lines-' );
2404: raise;
2405:
2406: END OE_Merge_Lines;
2407:
2571: customer_site_id_tab num_table_binary_int;
2572:
2573: BEGIN
2574:
2575: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_History()+' );
2576:
2577: IF (process_mode = 'LOCK') THEN
2578:
2579: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2575: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_History()+' );
2576:
2577: IF (process_mode = 'LOCK') THEN
2578:
2579: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2580: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
2581:
2582: open merge_lines_history;
2583: close merge_lines_history;
2576:
2577: IF (process_mode = 'LOCK') THEN
2578:
2579: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2580: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
2581:
2582: open merge_lines_history;
2583: close merge_lines_history;
2584:
2648: end loop;
2649: end if;
2650:
2651:
2652: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2653: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
2654:
2655: IF lhst_line_id_tab.COUNT <> 0 THEN
2656: FORALL i in lhst_line_id_tab.FIRST..lhst_line_id_tab.LAST
2649: end if;
2650:
2651:
2652: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2653: arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
2654:
2655: IF lhst_line_id_tab.COUNT <> 0 THEN
2656: FORALL i in lhst_line_id_tab.FIRST..lhst_line_id_tab.LAST
2657: UPDATE OE_ORDER_LINES_HISTORY LHIST
2676: ELSE
2677: g_count := 0;
2678: END IF;
2679:
2680: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2681: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2682: EXIT WHEN merge_lines_history_2%NOTFOUND;
2683:
2684: lhst_line_id_tab.DELETE;
2677: g_count := 0;
2678: END IF;
2679:
2680: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2681: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2682: EXIT WHEN merge_lines_history_2%NOTFOUND;
2683:
2684: lhst_line_id_tab.DELETE;
2685: lhst_sold_to_org_id_tab.DELETE;
2695:
2696: END IF;
2697: customer_id_tab.DELETE;
2698: customer_site_id_tab.DELETE;
2699: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_History()-' );
2700:
2701: EXCEPTION
2702: when others then
2703: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Lines_History-' );
2699: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_History()-' );
2700:
2701: EXCEPTION
2702: when others then
2703: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Lines_History-' );
2704: raise;
2705:
2706: END OE_Merge_Lines_History;
2707:
2763: and m.set_number = set_num)
2764: for update nowait;
2765:
2766: BEGIN
2767: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_IFACE()+' );
2768:
2769: -- both customer and site level
2770:
2771: IF( process_mode = 'LOCK' ) THEN
2769: -- both customer and site level
2770:
2771: IF( process_mode = 'LOCK' ) THEN
2772:
2773: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2774: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2775:
2776: open c1;
2777: close c1;
2770:
2771: IF( process_mode = 'LOCK' ) THEN
2772:
2773: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2774: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2775:
2776: open c1;
2777: close c1;
2778:
2786: close c4;
2787:
2788: ELSE
2789: -- site level update
2790: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2791: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2792:
2793: UPDATE oe_lines_iface_all a
2794: set ship_to_org_id = (select distinct m.customer_site_id
2787:
2788: ELSE
2789: -- site level update
2790: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2791: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2792:
2793: UPDATE oe_lines_iface_all a
2794: set ship_to_org_id = (select distinct m.customer_site_id
2795: from ra_customer_merges m
2809: where m.process_flag = 'N'
2810: and m.request_id = req_id
2811: and m.set_number = set_num);
2812: g_count := sql%rowcount;
2813: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2814: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2815:
2816: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2817: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2810: and m.request_id = req_id
2811: and m.set_number = set_num);
2812: g_count := sql%rowcount;
2813: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2814: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2815:
2816: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2817: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2818:
2812: g_count := sql%rowcount;
2813: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2814: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2815:
2816: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2817: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2818:
2819: UPDATE oe_lines_iface_all a
2820: set invoice_to_org_id = (select distinct m.customer_site_id
2813: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2814: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2815:
2816: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2817: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2818:
2819: UPDATE oe_lines_iface_all a
2820: set invoice_to_org_id = (select distinct m.customer_site_id
2821: from ra_customer_merges m
2835: where m.process_flag = 'N'
2836: and m.request_id = req_id
2837: and m.set_number = set_num);
2838: g_count := sql%rowcount;
2839: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2840: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2841:
2842: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2843: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2836: and m.request_id = req_id
2837: and m.set_number = set_num);
2838: g_count := sql%rowcount;
2839: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2840: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2841:
2842: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2843: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2844:
2838: g_count := sql%rowcount;
2839: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2840: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2841:
2842: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2843: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2844:
2845: UPDATE oe_lines_iface_all a
2846: set deliver_to_org_id = (select distinct m.customer_site_id
2839: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2840: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2841:
2842: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2843: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2844:
2845: UPDATE oe_lines_iface_all a
2846: set deliver_to_org_id = (select distinct m.customer_site_id
2847: from ra_customer_merges m
2861: where m.process_flag = 'N'
2862: and m.request_id = req_id
2863: and m.set_number = set_num);
2864: g_count := sql%rowcount;
2865: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2866: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2867:
2868: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2869: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2862: and m.request_id = req_id
2863: and m.set_number = set_num);
2864: g_count := sql%rowcount;
2865: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2866: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2867:
2868: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2869: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2870:
2864: g_count := sql%rowcount;
2865: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2866: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2867:
2868: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2869: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2870:
2871: -- customer level update
2872: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2865: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2866: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2867:
2868: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2869: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2870:
2871: -- customer level update
2872: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2873: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2868: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2869: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2870:
2871: -- customer level update
2872: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2873: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2874:
2875: UPDATE oe_lines_iface_all a
2876: set sold_to_org_id = (select distinct m.customer_id
2869: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2870:
2871: -- customer level update
2872: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2873: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
2874:
2875: UPDATE oe_lines_iface_all a
2876: set sold_to_org_id = (select distinct m.customer_id
2877: from ra_customer_merges m
2893: and m.set_number = set_num);
2894:
2895: g_count := sql%rowcount;
2896:
2897: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2898: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2899:
2900: END IF;
2901:
2894:
2895: g_count := sql%rowcount;
2896:
2897: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2898: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2899:
2900: END IF;
2901:
2902: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_IFACE()-' );
2898: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2899:
2900: END IF;
2901:
2902: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_IFACE()-' );
2903:
2904: EXCEPTION
2905: When others then
2906: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Lines_IFACE-' );
2902: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Lines_IFACE()-' );
2903:
2904: EXCEPTION
2905: When others then
2906: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Lines_IFACE-' );
2907: raise;
2908:
2909: END OE_Merge_Lines_IFACE;
2910:
3012:
3013: l_profile_val VARCHAR2(30);
3014:
3015: BEGIN
3016: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Line_ACKS()+' );
3017:
3018: /* both customer and site level */
3019:
3020: IF( process_mode = 'LOCK' ) THEN
3018: /* both customer and site level */
3019:
3020: IF( process_mode = 'LOCK' ) THEN
3021:
3022: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3023: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3024:
3025: open c1;
3026: close c1;
3019:
3020: IF( process_mode = 'LOCK' ) THEN
3021:
3022: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3023: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3024:
3025: open c1;
3026: close c1;
3027:
3089: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3090: end if;
3091:
3092: /* site level update */
3093: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3094: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3095:
3096: UPDATE oe_line_acks a
3097: set ship_to_org_id = (select distinct m.customer_site_id
3090: end if;
3091:
3092: /* site level update */
3093: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3094: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3095:
3096: UPDATE oe_line_acks a
3097: set ship_to_org_id = (select distinct m.customer_site_id
3098: from ra_customer_merges m
3113: and m.request_id = req_id
3114: and m.set_number = set_num)
3115: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3116: g_count := sql%rowcount;
3117: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3118: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3119:
3120: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3121: insert into HZ_CUSTOMER_MERGE_LOG (
3114: and m.set_number = set_num)
3115: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3116: g_count := sql%rowcount;
3117: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3118: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3119:
3120: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3121: insert into HZ_CUSTOMER_MERGE_LOG (
3122: MERGE_LOG_ID,
3157: and m.set_number = set_num
3158: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3159: end if;
3160:
3161: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3162: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3163:
3164: UPDATE oe_line_acks a
3165: set invoice_to_org_id = (select distinct m.customer_site_id
3158: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3159: end if;
3160:
3161: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3162: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3163:
3164: UPDATE oe_line_acks a
3165: set invoice_to_org_id = (select distinct m.customer_site_id
3166: from ra_customer_merges m
3181: and m.request_id = req_id
3182: and m.set_number = set_num)
3183: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3184: g_count := sql%rowcount;
3185: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3186: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3187:
3188: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3189: insert into HZ_CUSTOMER_MERGE_LOG (
3182: and m.set_number = set_num)
3183: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3184: g_count := sql%rowcount;
3185: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3186: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3187:
3188: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3189: insert into HZ_CUSTOMER_MERGE_LOG (
3190: MERGE_LOG_ID,
3225: and m.set_number = set_num
3226: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3227: end if;
3228:
3229: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3230: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3231:
3232: UPDATE oe_line_acks a
3233: set deliver_to_org_id = (select distinct m.customer_site_id
3226: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3227: end if;
3228:
3229: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3230: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3231:
3232: UPDATE oe_line_acks a
3233: set deliver_to_org_id = (select distinct m.customer_site_id
3234: from ra_customer_merges m
3249: and m.request_id = req_id
3250: and m.set_number = set_num)
3251: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3252: g_count := sql%rowcount;
3253: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3254: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3255:
3256: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3257: insert into HZ_CUSTOMER_MERGE_LOG (
3250: and m.set_number = set_num)
3251: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3252: g_count := sql%rowcount;
3253: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3254: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3255:
3256: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3257: insert into HZ_CUSTOMER_MERGE_LOG (
3258: MERGE_LOG_ID,
3293: and m.set_number = set_num
3294: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3295: end if;
3296:
3297: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3298: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3299:
3300:
3301: UPDATE oe_line_acks a
3294: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3295: end if;
3296:
3297: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3298: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3299:
3300:
3301: UPDATE oe_line_acks a
3302: set intmed_ship_to_org_id = (select distinct m.customer_site_id
3319: and m.set_number = set_num)
3320: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3321: g_count := sql%rowcount;
3322:
3323: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3324: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3325:
3326: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3327: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3320: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3321: g_count := sql%rowcount;
3322:
3323: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3324: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3325:
3326: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3327: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3328:
3322:
3323: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3324: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3325:
3326: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3327: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3328:
3329:
3330: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3323: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3324: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3325:
3326: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3327: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3328:
3329:
3330: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3331: insert into HZ_CUSTOMER_MERGE_LOG (
3368: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3369: end if;
3370:
3371: /* customer level update */
3372: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3373: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3374:
3375: UPDATE oe_line_acks a
3376: set sold_to_org_id = (select distinct m.customer_id
3369: end if;
3370:
3371: /* customer level update */
3372: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3373: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3374:
3375: UPDATE oe_line_acks a
3376: set sold_to_org_id = (select distinct m.customer_id
3377: from ra_customer_merges m
3396: and a.line_id in (select line_id from oe_order_lines);
3397:
3398: g_count := sql%rowcount;
3399:
3400: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3401: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3402:
3403: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3404: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3397:
3398: g_count := sql%rowcount;
3399:
3400: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3401: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3402:
3403: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3404: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3405:
3399:
3400: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3401: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3402:
3403: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3404: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3405:
3406:
3407: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3400: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3401: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3402:
3403: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3404: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3405:
3406:
3407: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3408: insert into HZ_CUSTOMER_MERGE_LOG (
3445: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3446: end if;
3447:
3448: /* customer level update */
3449: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3450: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3451:
3452: UPDATE oe_line_acks a
3453: set end_customer_site_use_id = (select distinct m.customer_id
3446: end if;
3447:
3448: /* customer level update */
3449: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3450: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3451:
3452: UPDATE oe_line_acks a
3453: set end_customer_site_use_id = (select distinct m.customer_id
3454: from ra_customer_merges m
3471: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3472:
3473: g_count := sql%rowcount;
3474:
3475: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3476: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3477:
3478: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3479: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3472:
3473: g_count := sql%rowcount;
3474:
3475: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3476: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3477:
3478: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3479: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3480:
3474:
3475: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3476: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3477:
3478: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3479: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3480:
3481:
3482: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3475: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3476: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3477:
3478: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3479: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3480:
3481:
3482: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3483: insert into HZ_CUSTOMER_MERGE_LOG (
3520: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3521: end if;
3522:
3523: /* customer level update */
3524: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3525: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3526:
3527: UPDATE oe_line_acks a
3528: set end_customer_id = (select distinct m.customer_id
3521: end if;
3522:
3523: /* customer level update */
3524: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3525: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3526:
3527: UPDATE oe_line_acks a
3528: set end_customer_id = (select distinct m.customer_id
3529: from ra_customer_merges m
3548: and a.line_id in (select line_id from oe_order_lines);
3549:
3550: g_count := sql%rowcount;
3551:
3552: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3553: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3554:
3555: END IF;
3556:
3549:
3550: g_count := sql%rowcount;
3551:
3552: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3553: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3554:
3555: END IF;
3556:
3557: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Line_ACKS()-' );
3553: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3554:
3555: END IF;
3556:
3557: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Line_ACKS()-' );
3558:
3559: EXCEPTION
3560: When others then
3561: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Line_ACKS-' );
3557: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Line_ACKS()-' );
3558:
3559: EXCEPTION
3560: When others then
3561: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Line_ACKS-' );
3562: raise;
3563:
3564: END OE_Merge_Line_ACKS;
3565: -----------------------------------------------------------------
3583: sql_stmnt varchar2(200);
3584:
3585: BEGIN
3586:
3587: arp_message.set_line( 'OE_CUST_MERGE.Merge()+' );
3588: -- sql_stmnt := 'Alter Session set hash_area_size=61440000';
3589: -- EXECUTE IMMEDIATE sql_stmnt;
3590: l_dbi_profile := NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'), 'Y');
3591: arp_message.set_line(' Dbi Profile='||l_dbi_profile);
3587: arp_message.set_line( 'OE_CUST_MERGE.Merge()+' );
3588: -- sql_stmnt := 'Alter Session set hash_area_size=61440000';
3589: -- EXECUTE IMMEDIATE sql_stmnt;
3590: l_dbi_profile := NVL(FND_PROFILE.VALUE('ONT_DBI_INSTALLED'), 'Y');
3591: arp_message.set_line(' Dbi Profile='||l_dbi_profile);
3592: OE_CUST_MERGE.OE_Attachment_Merge (Req_Id, Set_Num, Process_Mode);
3593: OE_CUST_MERGE.OE_Defaulting_Merge (Req_Id, Set_Num, Process_Mode);
3594: OE_CUST_MERGE.OE_Hold_Merge (Req_Id, Set_Num, Process_Mode);
3595: OE_CUST_MERGE.OE_Constraints_Merge (Req_Id, Set_Num, Process_Mode);
3597: -- drop ship are po line location id and not site ids of accounts
3598: --OE_CUST_MERGE.OE_Drop_Ship_Merge (Req_Id, Set_Num, Process_Mode);
3599: OE_CUST_MERGE.OE_Ship_Tolerance_Merge (Req_Id, Set_Num, Process_Mode);
3600: OE_CUST_MERGE.OE_Order_Merge (Req_Id, Set_Num, Process_Mode);
3601: arp_message.set_line( 'OE_CUST_MERGE.Merge()-' );
3602:
3603: /* this part will be calling other internal procedures */
3604:
3605: EXCEPTION
3603: /* this part will be calling other internal procedures */
3604:
3605: EXCEPTION
3606: When others then
3607: arp_message.set_error( 'OE_CUST_MERGE.Merge-' );
3608: raise;
3609:
3610:
3611: END Merge;
3651:
3652: l_profile_val VARCHAR2(30);
3653:
3654: BEGIN
3655: arp_message.set_line( 'OE_CUST_MERGE.OE_Attachment_Merge()+' );
3656:
3657: /*-----------------------------+
3658: | OE_ATTACHMENTS_RULE_ELEMENTS|
3659: +-----------------------------*/
3660: /* both customer and site level */
3661:
3662: IF( process_mode = 'LOCK' ) THEN
3663:
3664: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3665: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
3666:
3667: open c1;
3668: close c1;
3661:
3662: IF( process_mode = 'LOCK' ) THEN
3663:
3664: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3665: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
3666:
3667: open c1;
3668: close c1;
3669:
3719:
3720: END IF;
3721:
3722: /* site level update */
3723: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3724: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
3725:
3726: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
3727: set (attribute_value) = (select distinct to_char(m.customer_site_id)
3720: END IF;
3721:
3722: /* site level update */
3723: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3724: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
3725:
3726: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
3727: set (attribute_value) = (select distinct to_char(m.customer_site_id)
3728: from ra_customer_merges m
3742: and attribute_code = 'SHIP_TO_ORG_ID';
3743:
3744: g_count := sql%rowcount;
3745:
3746: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3747: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3748:
3749:
3750: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3743:
3744: g_count := sql%rowcount;
3745:
3746: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3747: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3748:
3749:
3750: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3751: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
3787:
3788: END IF;
3789:
3790: /* site level update */
3791: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3792: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
3793:
3794: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
3795: set (attribute_value) = (select distinct to_char(m.customer_site_id)
3788: END IF;
3789:
3790: /* site level update */
3791: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3792: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
3793:
3794: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
3795: set (attribute_value) = (select distinct to_char(m.customer_site_id)
3796: from ra_customer_merges m
3810: and attribute_code = 'INVOICE_TO_ORG_ID';
3811:
3812: g_count := sql%rowcount;
3813:
3814: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3815: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3816:
3817:
3818:
3811:
3812: g_count := sql%rowcount;
3813:
3814: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3815: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3816:
3817:
3818:
3819: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3856:
3857: END IF;
3858:
3859: /* customer level update */
3860: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3861: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
3862:
3863: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
3864: set (attribute_value) = (select distinct to_char(m.customer_id)
3857: END IF;
3858:
3859: /* customer level update */
3860: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3861: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
3862:
3863: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
3864: set (attribute_value) = (select distinct to_char(m.customer_id)
3865: from ra_customer_merges m
3880:
3881:
3882: g_count := sql%rowcount;
3883:
3884: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3885: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3886:
3887: END IF;
3888:
3881:
3882: g_count := sql%rowcount;
3883:
3884: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3885: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3886:
3887: END IF;
3888:
3889: arp_message.set_line( 'OE_CUST_MERGE.OE_Attachment_Merge()-' );
3885: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3886:
3887: END IF;
3888:
3889: arp_message.set_line( 'OE_CUST_MERGE.OE_Attachment_Merge()-' );
3890:
3891:
3892: EXCEPTION
3893: when others then
3890:
3891:
3892: EXCEPTION
3893: when others then
3894: arp_message.set_line( 'OE_CUST_MERGE.OE_Attachment_Merge' );
3895: raise;
3896:
3897:
3898: END OE_Attachment_Merge;
3993:
3994: l_profile_val VARCHAR2(30);
3995:
3996: BEGIN
3997: arp_message.set_line( 'OE_CUST_MERGE.OE_Defaulting_Merge()+' );
3998:
3999: /*-----------------------------+
4000: | OE_DEF_CONDN_ELEMS|
4001: +-----------------------------*/
4002: /* both customer and site level */
4003:
4004: IF( process_mode = 'LOCK' ) THEN
4005:
4006: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
4007: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4008:
4009:
4010: open c1;
4003:
4004: IF( process_mode = 'LOCK' ) THEN
4005:
4006: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
4007: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4008:
4009:
4010: open c1;
4011: close c1;
4076:
4077: END IF;
4078:
4079: /* site level update */
4080: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4081: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4082:
4083: UPDATE OE_DEF_CONDN_ELEMS a
4084: set value_string = (select distinct to_char(m.customer_site_id)
4077: END IF;
4078:
4079: /* site level update */
4080: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4081: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4082:
4083: UPDATE OE_DEF_CONDN_ELEMS a
4084: set value_string = (select distinct to_char(m.customer_site_id)
4085: from ra_customer_merges m
4100: and attribute_code = 'SHIP_TO_ORG_ID';
4101:
4102: g_count := sql%rowcount;
4103:
4104: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4105: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4106: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4107: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4108: MERGE_LOG_ID,
4101:
4102: g_count := sql%rowcount;
4103:
4104: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4105: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4106: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4107: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4108: MERGE_LOG_ID,
4109: TABLE_NAME,
4144: END IF;
4145:
4146:
4147: /* site level update */
4148: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4149: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4150:
4151: UPDATE OE_DEF_CONDN_ELEMS a
4152: set value_string = (select distinct to_char(m.customer_site_id)
4145:
4146:
4147: /* site level update */
4148: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4149: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4150:
4151: UPDATE OE_DEF_CONDN_ELEMS a
4152: set value_string = (select distinct to_char(m.customer_site_id)
4153: from ra_customer_merges m
4168: and attribute_code = 'INVOICE_TO_ORG_ID';
4169:
4170: g_count := sql%rowcount;
4171:
4172: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4173: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4174:
4175: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4176: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4169:
4170: g_count := sql%rowcount;
4171:
4172: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4173: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4174:
4175: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4176: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4177: MERGE_LOG_ID,
4212:
4213: END IF;
4214:
4215: /* site level update */
4216: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4217: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4218:
4219: UPDATE OE_DEF_CONDN_ELEMS a
4220: set value_string = (select distinct to_char(m.customer_site_id)
4213: END IF;
4214:
4215: /* site level update */
4216: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4217: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4218:
4219: UPDATE OE_DEF_CONDN_ELEMS a
4220: set value_string = (select distinct to_char(m.customer_site_id)
4221: from ra_customer_merges m
4236: and attribute_code = 'INTMED_SHIP_TO_ORG_ID';
4237:
4238: g_count := sql%rowcount;
4239:
4240: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4241: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4242:
4243: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4244: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4237:
4238: g_count := sql%rowcount;
4239:
4240: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4241: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4242:
4243: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4244: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4245: MERGE_LOG_ID,
4280:
4281: END IF;
4282:
4283: /* customer level update */
4284: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4285: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4286:
4287: UPDATE OE_DEF_CONDN_ELEMS a
4288: set value_string = (select distinct to_char(m.customer_id)
4281: END IF;
4282:
4283: /* customer level update */
4284: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4285: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4286:
4287: UPDATE OE_DEF_CONDN_ELEMS a
4288: set value_string = (select distinct to_char(m.customer_id)
4289: from ra_customer_merges m
4305:
4306:
4307: g_count := sql%rowcount;
4308:
4309: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4310: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4311:
4312:
4313: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4306:
4307: g_count := sql%rowcount;
4308:
4309: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4310: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4311:
4312:
4313: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4314: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4350:
4351: END IF;
4352:
4353: /* site level update */
4354: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4355: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4356:
4357:
4358:
4351: END IF;
4352:
4353: /* site level update */
4354: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4355: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4356:
4357:
4358:
4359: UPDATE OE_DEF_ATTR_DEF_RULES a
4376: and attribute_code = 'SHIP_TO_ORG_ID';
4377:
4378: g_count := sql%rowcount;
4379:
4380: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4381: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4382:
4383: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4384: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4377:
4378: g_count := sql%rowcount;
4379:
4380: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4381: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4382:
4383: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4384: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4385: MERGE_LOG_ID,
4420:
4421: END IF;
4422:
4423: /* site level update */
4424: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4425: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4426:
4427: UPDATE OE_DEF_ATTR_DEF_RULES a
4428: set src_constant_value = (select distinct to_char(m.customer_site_id)
4421: END IF;
4422:
4423: /* site level update */
4424: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4425: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4426:
4427: UPDATE OE_DEF_ATTR_DEF_RULES a
4428: set src_constant_value = (select distinct to_char(m.customer_site_id)
4429: from ra_customer_merges m
4444: and attribute_code = 'INVOICE_TO_ORG_ID';
4445:
4446: g_count := sql%rowcount;
4447:
4448: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4449: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4450:
4451: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4452: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4445:
4446: g_count := sql%rowcount;
4447:
4448: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4449: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4450:
4451: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4452: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4453: MERGE_LOG_ID,
4488:
4489: END IF;
4490:
4491: /* site level update */
4492: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4493: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4494:
4495: UPDATE OE_DEF_ATTR_DEF_RULES a
4496: set src_constant_value = (select distinct to_char(m.customer_site_id)
4489: END IF;
4490:
4491: /* site level update */
4492: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4493: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4494:
4495: UPDATE OE_DEF_ATTR_DEF_RULES a
4496: set src_constant_value = (select distinct to_char(m.customer_site_id)
4497: from ra_customer_merges m
4512: and attribute_code = 'INTMED_SHIP_TO_ORG_ID';
4513:
4514: g_count := sql%rowcount;
4515:
4516: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4517: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4518:
4519:
4520: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4513:
4514: g_count := sql%rowcount;
4515:
4516: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4517: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4518:
4519:
4520: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4521: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4557:
4558: END IF;
4559:
4560: /* customer level update */
4561: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4562: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4563:
4564: UPDATE OE_DEF_ATTR_DEF_RULES a
4565: set src_constant_value = (select distinct to_char(m.customer_id)
4558: END IF;
4559:
4560: /* customer level update */
4561: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4562: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4563:
4564: UPDATE OE_DEF_ATTR_DEF_RULES a
4565: set src_constant_value = (select distinct to_char(m.customer_id)
4566: from ra_customer_merges m
4582:
4583:
4584: g_count := sql%rowcount;
4585:
4586: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4587: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4588:
4589:
4590:
4583:
4584: g_count := sql%rowcount;
4585:
4586: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4587: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4588:
4589:
4590:
4591: END IF;
4589:
4590:
4591: END IF;
4592:
4593: arp_message.set_line( 'OE_CUST_MERGE.OE_Defaulting_Merge()-' );
4594:
4595:
4596: EXCEPTION
4597: when others then
4594:
4595:
4596: EXCEPTION
4597: when others then
4598: arp_message.set_line( 'OE_CUST_MERGE.OE_Defaulting_Merge' );
4599: raise;
4600:
4601:
4602:
4653:
4654: l_profile_val VARCHAR2(30);
4655:
4656: BEGIN
4657: arp_message.set_line( 'OE_CUST_MERGE.Constraints_Merge()+' );
4658:
4659: /*-----------------------------+
4660: | oe_pc_vtmplt_cols|
4661: +-----------------------------*/
4662: /* both customer and site level */
4663:
4664: IF( process_mode = 'LOCK' ) THEN
4665:
4666: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
4667: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
4668:
4669:
4670: open c1;
4663:
4664: IF( process_mode = 'LOCK' ) THEN
4665:
4666: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
4667: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
4668:
4669:
4670: open c1;
4671: close c1;
4725:
4726: END IF;
4727:
4728: /* site level update */
4729: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4730: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
4731:
4732: UPDATE OE_PC_VTMPLT_COLS a
4733: set value_string = (select distinct to_char(m.customer_site_id)
4726: END IF;
4727:
4728: /* site level update */
4729: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4730: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
4731:
4732: UPDATE OE_PC_VTMPLT_COLS a
4733: set value_string = (select distinct to_char(m.customer_site_id)
4734: from ra_customer_merges m
4749: and column_name = 'SHIP_TO_ORG_ID';
4750:
4751: g_count := sql%rowcount;
4752:
4753: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4754: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4755:
4756: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4757: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4750:
4751: g_count := sql%rowcount;
4752:
4753: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4754: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4755:
4756: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4757: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4758: MERGE_LOG_ID,
4793:
4794: END IF;
4795:
4796: /* site level update */
4797: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4798: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
4799:
4800: UPDATE OE_PC_VTMPLT_COLS a
4801: set value_string = (select distinct to_char(m.customer_site_id)
4794: END IF;
4795:
4796: /* site level update */
4797: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4798: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
4799:
4800: UPDATE OE_PC_VTMPLT_COLS a
4801: set value_string = (select distinct to_char(m.customer_site_id)
4802: from ra_customer_merges m
4817: and column_name = 'INVOICE_TO_ORG_ID';
4818:
4819: g_count := sql%rowcount;
4820:
4821: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4822: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4823:
4824: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4825: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4818:
4819: g_count := sql%rowcount;
4820:
4821: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4822: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4823:
4824: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4825: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4826: MERGE_LOG_ID,
4861:
4862: END IF;
4863:
4864: /* site level update */
4865: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4866: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
4867:
4868: UPDATE OE_PC_VTMPLT_COLS a
4869: set value_string = (select distinct to_char(m.customer_site_id)
4862: END IF;
4863:
4864: /* site level update */
4865: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4866: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
4867:
4868: UPDATE OE_PC_VTMPLT_COLS a
4869: set value_string = (select distinct to_char(m.customer_site_id)
4870: from ra_customer_merges m
4885: and column_name = 'INTMED_SHIP_TO_ORG_ID';
4886:
4887: g_count := sql%rowcount;
4888:
4889: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4890: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4891:
4892:
4893: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4886:
4887: g_count := sql%rowcount;
4888:
4889: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4890: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4891:
4892:
4893: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
4894: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
4930:
4931: END IF;
4932:
4933: /* customer level update */
4934: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4935: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
4936:
4937: UPDATE OE_PC_VTMPLT_COLS a
4938: set value_string = (select distinct to_char(m.customer_id)
4931: END IF;
4932:
4933: /* customer level update */
4934: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
4935: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
4936:
4937: UPDATE OE_PC_VTMPLT_COLS a
4938: set value_string = (select distinct to_char(m.customer_id)
4939: from ra_customer_merges m
4955:
4956:
4957: g_count := sql%rowcount;
4958:
4959: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4960: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4961:
4962: END IF;
4963:
4956:
4957: g_count := sql%rowcount;
4958:
4959: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
4960: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4961:
4962: END IF;
4963:
4964: arp_message.set_line( 'OE_CUST_MERGE.OE_Constraints_Merge()-' );
4960: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
4961:
4962: END IF;
4963:
4964: arp_message.set_line( 'OE_CUST_MERGE.OE_Constraints_Merge()-' );
4965:
4966:
4967: EXCEPTION
4968: when others then
4965:
4966:
4967: EXCEPTION
4968: when others then
4969: arp_message.set_line( 'OE_CUST_MERGE.OE_Constraints_Merge' );
4970: raise;
4971:
4972: END OE_Constraints_Merge;
4973:
5016: l_profile_val VARCHAR2(30);
5017:
5018: BEGIN
5019:
5020: arp_message.set_line( 'OE_CUST_MERGE.OE_Hold_Merge()+' );
5021: /*-----------------------------+
5022: | OE_HOLD_SOURCES |
5023: +-----------------------------*/
5024: /* both customer and site level */
5024: /* both customer and site level */
5025:
5026: IF( process_mode = 'LOCK' ) THEN
5027:
5028: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5029: arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
5030:
5031: open c1;
5032: close c1;
5025:
5026: IF( process_mode = 'LOCK' ) THEN
5027:
5028: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5029: arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
5030:
5031: open c1;
5032: close c1;
5033:
5082:
5083: END IF;
5084:
5085: /* site level update */
5086: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5087: arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
5088:
5089: UPDATE OE_HOLD_SOURCES a
5090: set (hold_entity_id) = (select distinct m.customer_site_id
5083: END IF;
5084:
5085: /* site level update */
5086: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5087: arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
5088:
5089: UPDATE OE_HOLD_SOURCES a
5090: set (hold_entity_id) = (select distinct m.customer_site_id
5091: from ra_customer_merges m
5106: and hold_entity_code = 'S';
5107:
5108: g_count := sql%rowcount;
5109:
5110: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5111: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5112:
5113: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5114: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5107:
5108: g_count := sql%rowcount;
5109:
5110: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5111: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5112:
5113: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5114: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5115: MERGE_LOG_ID,
5150:
5151: END IF;
5152:
5153: /* site level update */
5154: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5155: arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
5156:
5157: UPDATE OE_HOLD_SOURCES a
5158: set (hold_entity_id) = (select distinct m.customer_site_id
5151: END IF;
5152:
5153: /* site level update */
5154: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5155: arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
5156:
5157: UPDATE OE_HOLD_SOURCES a
5158: set (hold_entity_id) = (select distinct m.customer_site_id
5159: from ra_customer_merges m
5174: and hold_entity_code = 'B';
5175:
5176: g_count := sql%rowcount;
5177:
5178: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5179: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5180:
5181:
5182: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5175:
5176: g_count := sql%rowcount;
5177:
5178: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5179: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5180:
5181:
5182: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5183: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5219:
5220: END IF;
5221:
5222: /* customer level update */
5223: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5224: arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
5225:
5226: UPDATE OE_HOLD_SOURCES a
5227: set hold_entity_id = (select distinct m.customer_id
5220: END IF;
5221:
5222: /* customer level update */
5223: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5224: arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
5225:
5226: UPDATE OE_HOLD_SOURCES a
5227: set hold_entity_id = (select distinct m.customer_id
5228: from ra_customer_merges m
5242: and hold_entity_code = 'C';
5243:
5244: g_count := sql%rowcount;
5245:
5246: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5247: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5248:
5249: END IF;
5250:
5243:
5244: g_count := sql%rowcount;
5245:
5246: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5247: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5248:
5249: END IF;
5250:
5251: arp_message.set_line( 'OE_CUST_MERGE.OE_Hold_Merge()-' );
5247: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5248:
5249: END IF;
5250:
5251: arp_message.set_line( 'OE_CUST_MERGE.OE_Hold_Merge()-' );
5252:
5253:
5254: EXCEPTION
5255: when others then
5252:
5253:
5254: EXCEPTION
5255: when others then
5256: arp_message.set_line( 'OE_CUST_MERGE.OE_Hold_Merge' );
5257: raise;
5258:
5259: END OE_Hold_Merge;
5260:
5275:
5276: l_profile_val VARCHAR2(30);
5277:
5278: BEGIN
5279: arp_message.set_line( 'OE_CUST_MERGE.OE_Drop_SHip_Merge()+' );
5280:
5281: /* both customer and site level */
5282:
5283: IF( process_mode = 'LOCK' ) THEN
5281: /* both customer and site level */
5282:
5283: IF( process_mode = 'LOCK' ) THEN
5284:
5285: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5286: arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
5287:
5288: open c1;
5289: close c1;
5282:
5283: IF( process_mode = 'LOCK' ) THEN
5284:
5285: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5286: arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
5287:
5288: open c1;
5289: close c1;
5290:
5332:
5333: END IF;
5334:
5335: /* site level update */
5336: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5337: arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
5338:
5339: UPDATE OE_DROP_SHIP_SOURCES a
5340: set line_location_id = (select distinct m.customer_site_id
5333: END IF;
5334:
5335: /* site level update */
5336: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5337: arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
5338:
5339: UPDATE OE_DROP_SHIP_SOURCES a
5340: set line_location_id = (select distinct m.customer_site_id
5341: from ra_customer_merges m
5351: where m.process_flag = 'N'
5352: and m.request_id = req_id
5353: and m.set_number = set_num);
5354: g_count := sql%rowcount;
5355: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5356: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5357:
5358: END IF;
5359:
5352: and m.request_id = req_id
5353: and m.set_number = set_num);
5354: g_count := sql%rowcount;
5355: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5356: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5357:
5358: END IF;
5359:
5360: arp_message.set_line( 'OE_CUST_MERGE.OE_Drop_SHip_Merge()-' );
5356: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5357:
5358: END IF;
5359:
5360: arp_message.set_line( 'OE_CUST_MERGE.OE_Drop_SHip_Merge()-' );
5361:
5362: EXCEPTION
5363: When others then
5364: arp_message.set_error( 'OE_CUST_MERGE.OE_Drop_SHip_Merge-' );
5360: arp_message.set_line( 'OE_CUST_MERGE.OE_Drop_SHip_Merge()-' );
5361:
5362: EXCEPTION
5363: When others then
5364: arp_message.set_error( 'OE_CUST_MERGE.OE_Drop_SHip_Merge-' );
5365: raise;
5366:
5367: END OE_Drop_Ship_Merge;
5368:
5394:
5395: l_profile_val VARCHAR2(30);
5396:
5397: BEGIN
5398: arp_message.set_line( 'OE_CUST_MERGE.OE_ship_tolerance_merge()+' );
5399:
5400: /* both customer and site level */
5401:
5402: IF( process_mode = 'LOCK' ) THEN
5400: /* both customer and site level */
5401:
5402: IF( process_mode = 'LOCK' ) THEN
5403:
5404: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5405: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
5406:
5407: open c1;
5408: close c1;
5401:
5402: IF( process_mode = 'LOCK' ) THEN
5403:
5404: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5405: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
5406:
5407: open c1;
5408: close c1;
5409:
5456:
5457: END IF;
5458:
5459: /* site level update */
5460: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5461: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
5462:
5463: UPDATE OE_CUST_ITEM_SETTINGS a
5464: set site_use_id = (select distinct m.customer_site_id
5457: END IF;
5458:
5459: /* site level update */
5460: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5461: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
5462:
5463: UPDATE OE_CUST_ITEM_SETTINGS a
5464: set site_use_id = (select distinct m.customer_site_id
5465: from ra_customer_merges m
5475: where m.process_flag = 'N'
5476: and m.request_id = req_id
5477: and m.set_number = set_num);
5478: g_count := sql%rowcount;
5479: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5480: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5481:
5482: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5483: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5476: and m.request_id = req_id
5477: and m.set_number = set_num);
5478: g_count := sql%rowcount;
5479: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5480: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5481:
5482: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
5483: INSERT INTO HZ_CUSTOMER_MERGE_LOG (
5484: MERGE_LOG_ID,
5518:
5519: END IF;
5520:
5521: /* customer level update */
5522: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5523: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
5524:
5525: UPDATE OE_CUST_ITEM_SETTINGS a
5526: set customer_id = (select distinct m.customer_id
5519: END IF;
5520:
5521: /* customer level update */
5522: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5523: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
5524:
5525: UPDATE OE_CUST_ITEM_SETTINGS a
5526: set customer_id = (select distinct m.customer_id
5527: from ra_customer_merges m
5537: where m.process_flag = 'N'
5538: and m.request_id = req_id
5539: and m.set_number = set_num);
5540: g_count := sql%rowcount;
5541: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5542: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5543:
5544: END IF;
5545:
5538: and m.request_id = req_id
5539: and m.set_number = set_num);
5540: g_count := sql%rowcount;
5541: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5542: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5543:
5544: END IF;
5545:
5546: arp_message.set_line( 'OE_CUST_MERGE.OE_Ship_Tolerance_Merge()-' );
5542: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5543:
5544: END IF;
5545:
5546: arp_message.set_line( 'OE_CUST_MERGE.OE_Ship_Tolerance_Merge()-' );
5547:
5548: EXCEPTION
5549: When others then
5550: arp_message.set_error( 'OE_CUST_MERGE.OE_Ship_Tolerance_Merge-' );
5546: arp_message.set_line( 'OE_CUST_MERGE.OE_Ship_Tolerance_Merge()-' );
5547:
5548: EXCEPTION
5549: When others then
5550: arp_message.set_error( 'OE_CUST_MERGE.OE_Ship_Tolerance_Merge-' );
5551: raise;
5552:
5553: END OE_Ship_Tolerance_Merge;
5554:
5593: l_profile_val VARCHAR2(30);
5594:
5595: BEGIN
5596:
5597: arp_message.set_line( 'OE_CUST_MERGE.OE_Sets_Merge()+' );
5598:
5599: IF (process_mode = 'LOCK') THEN
5600:
5601: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5597: arp_message.set_line( 'OE_CUST_MERGE.OE_Sets_Merge()+' );
5598:
5599: IF (process_mode = 'LOCK') THEN
5600:
5601: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5602: arp_message.set_token( 'TABLE_NAME', 'oe_sets', FALSE );
5603:
5604: open sets_merge;
5605: close sets_merge;
5598:
5599: IF (process_mode = 'LOCK') THEN
5600:
5601: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5602: arp_message.set_token( 'TABLE_NAME', 'oe_sets', FALSE );
5603:
5604: open sets_merge;
5605: close sets_merge;
5606:
5652: end if;
5653: end if;
5654:
5655:
5656: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5657: arp_message.set_token( 'TABLE_NAME', 'oe_sets', FALSE );
5658:
5659: IF sets_set_id_tab.COUNT <> 0 THEN
5660: FORALL i in sets_set_id_tab.FIRST..sets_set_id_tab.LAST
5653: end if;
5654:
5655:
5656: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5657: arp_message.set_token( 'TABLE_NAME', 'oe_sets', FALSE );
5658:
5659: IF sets_set_id_tab.COUNT <> 0 THEN
5660: FORALL i in sets_set_id_tab.FIRST..sets_set_id_tab.LAST
5661: UPDATE OE_SETS S
5670: ELSE
5671: g_count := 0;
5672: END IF;
5673:
5674: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5675: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5676: EXIT WHEN sets_merge_2%NOTFOUND;
5677:
5678: sets_set_id_tab.DELETE;
5671: g_count := 0;
5672: END IF;
5673:
5674: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5675: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5676: EXIT WHEN sets_merge_2%NOTFOUND;
5677:
5678: sets_set_id_tab.DELETE;
5679: sets_ship_to_org_id_tab.DELETE;
5683:
5684: CLOSE sets_merge_2;
5685:
5686: END IF;
5687: arp_message.set_line( 'OE_CUST_MERGE.OE_Sets_Merge()-' );
5688:
5689: EXCEPTION
5690: when others then
5691: arp_message.set_error( 'OE_CUST_MERGE.OE_Sets_Merge-' );
5687: arp_message.set_line( 'OE_CUST_MERGE.OE_Sets_Merge()-' );
5688:
5689: EXCEPTION
5690: when others then
5691: arp_message.set_error( 'OE_CUST_MERGE.OE_Sets_Merge-' );
5692: raise;
5693:
5694: END OE_Sets_merge;
5695:
5761: l_profile_val varchar2(30);
5762:
5763: BEGIN
5764:
5765: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs()+' );
5766: IF (process_mode = 'LOCK') THEN
5767:
5768: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5769: arp_message.set_token( 'TABLE_NAME', 'oe_price_adj_attribs', FALSE );
5764:
5765: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs()+' );
5766: IF (process_mode = 'LOCK') THEN
5767:
5768: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5769: arp_message.set_token( 'TABLE_NAME', 'oe_price_adj_attribs', FALSE );
5770:
5771: open c1;
5772: close c1;
5765: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs()+' );
5766: IF (process_mode = 'LOCK') THEN
5767:
5768: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
5769: arp_message.set_token( 'TABLE_NAME', 'oe_price_adj_attribs', FALSE );
5770:
5771: open c1;
5772: close c1;
5773:
5820:
5821: END IF;
5822: end if;
5823:
5824: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5825: arp_message.set_token( 'TABLE_NAME', 'oe_price_adj_attribs', FALSE );
5826:
5827: IF attrib_id_tab.COUNT <> 0 THEN
5828: FORALL i in attrib_id_tab.FIRST..attrib_id_tab.LAST
5821: END IF;
5822: end if;
5823:
5824: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
5825: arp_message.set_token( 'TABLE_NAME', 'oe_price_adj_attribs', FALSE );
5826:
5827: IF attrib_id_tab.COUNT <> 0 THEN
5828: FORALL i in attrib_id_tab.FIRST..attrib_id_tab.LAST
5829: UPDATE OE_PRICE_ADJ_ATTRIBS S
5838: ELSE
5839: g_count := 0;
5840: END IF;
5841:
5842: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5843: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5844: EXIT WHEN c1_2%NOTFOUND;
5845:
5846: attrib_id_tab.DELETE;
5839: g_count := 0;
5840: END IF;
5841:
5842: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
5843: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
5844: EXIT WHEN c1_2%NOTFOUND;
5845:
5846: attrib_id_tab.DELETE;
5847: attrib_value_from_tab.DELETE;
5852:
5853: CLOSE c1_2;
5854:
5855: END IF;
5856: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs()-' );
5857:
5858: EXCEPTION
5859: when others then
5860: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs-' );
5856: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs()-' );
5857:
5858: EXCEPTION
5859: when others then
5860: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Price_Adj_Attribs-' );
5861: raise;
5862:
5863: END OE_Merge_Price_Adj_Attribs;
5864:
6001:
6002: l_profile_val VARCHAR2(30);
6003: BEGIN
6004:
6005: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Headers()+' );
6006:
6007: IF (process_mode = 'LOCK') THEN
6008:
6009: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6005: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Headers()+' );
6006:
6007: IF (process_mode = 'LOCK') THEN
6008:
6009: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6010: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_ALL', FALSE );
6011:
6012: open merge_headers;
6013: close merge_headers;
6006:
6007: IF (process_mode = 'LOCK') THEN
6008:
6009: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6010: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_ALL', FALSE );
6011:
6012: open merge_headers;
6013: close merge_headers;
6014:
6131: end if;
6132: end if;
6133:
6134:
6135: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6136: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_all', FALSE );
6137:
6138: IF hdr_header_id_tab.COUNT <> 0 THEN
6139:
6132: end if;
6133:
6134:
6135: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6136: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_all', FALSE );
6137:
6138: IF hdr_header_id_tab.COUNT <> 0 THEN
6139:
6140: FORALL i in hdr_header_id_tab.FIRST..hdr_header_id_tab.LAST
6159: ELSE
6160: g_count := 0;
6161: END IF;
6162:
6163: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6164: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6165: EXIT WHEN merge_headers_2%NOTFOUND;
6166:
6167: hdr_header_id_tab.DELETE;
6160: g_count := 0;
6161: END IF;
6162:
6163: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6164: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6165: EXIT WHEN merge_headers_2%NOTFOUND;
6166:
6167: hdr_header_id_tab.DELETE;
6168: hdr_sold_to_org_id_tab.DELETE;
6183:
6184: END IF;
6185: customer_id_tab.DELETE;
6186: customer_site_id_tab.DELETE;
6187: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Headers()-' );
6188:
6189: EXCEPTION
6190: when others then
6191: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blanket_Headers-' );
6187: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Headers()-' );
6188:
6189: EXCEPTION
6190: when others then
6191: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blanket_Headers-' );
6192: raise;
6193:
6194: END OE_Merge_Blanket_Headers;
6195:
6277:
6278: l_profile_val VARCHAR2(30);
6279:
6280: BEGIN
6281: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Hdrs_Hist()+' );
6282:
6283: -- both customer and site level
6284:
6285: IF( process_mode = 'LOCK' ) THEN
6283: -- both customer and site level
6284:
6285: IF( process_mode = 'LOCK' ) THEN
6286:
6287: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6288: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6289:
6290: open c1;
6291: close c1;
6284:
6285: IF( process_mode = 'LOCK' ) THEN
6286:
6287: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6288: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6289:
6290: open c1;
6291: close c1;
6292:
6347: and m.set_number = set_num;
6348: end if;
6349:
6350: -- site level update
6351: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6352: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6353:
6354: UPDATE oe_blanket_headers_hist a
6355: set ship_to_org_id = (select distinct m.customer_site_id
6348: end if;
6349:
6350: -- site level update
6351: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6352: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6353:
6354: UPDATE oe_blanket_headers_hist a
6355: set ship_to_org_id = (select distinct m.customer_site_id
6356: from ra_customer_merges m
6370: where m.process_flag = 'N'
6371: and m.request_id = req_id
6372: and m.set_number = set_num);
6373: g_count := sql%rowcount;
6374: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6375: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6376:
6377: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
6378: insert into HZ_CUSTOMER_MERGE_LOG (
6371: and m.request_id = req_id
6372: and m.set_number = set_num);
6373: g_count := sql%rowcount;
6374: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6375: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6376:
6377: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
6378: insert into HZ_CUSTOMER_MERGE_LOG (
6379: MERGE_LOG_ID,
6413: and m.request_id = req_id
6414: and m.set_number = set_num;
6415: end if;
6416:
6417: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6418: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6419:
6420: UPDATE oe_blanket_headers_hist a
6421: set invoice_to_org_id = (select distinct m.customer_site_id
6414: and m.set_number = set_num;
6415: end if;
6416:
6417: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6418: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6419:
6420: UPDATE oe_blanket_headers_hist a
6421: set invoice_to_org_id = (select distinct m.customer_site_id
6422: from ra_customer_merges m
6436: where m.process_flag = 'N'
6437: and m.request_id = req_id
6438: and m.set_number = set_num);
6439: g_count := sql%rowcount;
6440: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6441: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6442:
6443:
6444: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
6437: and m.request_id = req_id
6438: and m.set_number = set_num);
6439: g_count := sql%rowcount;
6440: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6441: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6442:
6443:
6444: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
6445: insert into HZ_CUSTOMER_MERGE_LOG (
6479: and m.process_flag = 'N'
6480: and m.request_id = req_id
6481: and m.set_number = set_num;
6482: end if;
6483: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6484: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6485:
6486: UPDATE oe_blanket_headers_hist a
6487: set deliver_to_org_id = (select distinct m.customer_site_id
6480: and m.request_id = req_id
6481: and m.set_number = set_num;
6482: end if;
6483: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6484: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6485:
6486: UPDATE oe_blanket_headers_hist a
6487: set deliver_to_org_id = (select distinct m.customer_site_id
6488: from ra_customer_merges m
6502: where m.process_flag = 'N'
6503: and m.request_id = req_id
6504: and m.set_number = set_num);
6505: g_count := sql%rowcount;
6506: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6507: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6508:
6509: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6510: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6503: and m.request_id = req_id
6504: and m.set_number = set_num);
6505: g_count := sql%rowcount;
6506: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6507: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6508:
6509: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6510: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6511:
6505: g_count := sql%rowcount;
6506: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6507: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6508:
6509: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6510: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6511:
6512:
6513: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
6506: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6507: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6508:
6509: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6510: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6511:
6512:
6513: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
6514: insert into HZ_CUSTOMER_MERGE_LOG (
6550: and m.set_number = set_num;
6551: end if;
6552: -- customer level update --
6553:
6554: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6555: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6556:
6557: UPDATE oe_blanket_headers_hist a
6558: set sold_to_org_id = (select distinct m.customer_id
6551: end if;
6552: -- customer level update --
6553:
6554: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6555: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
6556:
6557: UPDATE oe_blanket_headers_hist a
6558: set sold_to_org_id = (select distinct m.customer_id
6559: from ra_customer_merges m
6575: and m.set_number = set_num);
6576:
6577: g_count := sql%rowcount;
6578:
6579: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6580: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6581:
6582: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6583: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
6576:
6577: g_count := sql%rowcount;
6578:
6579: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6580: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6581:
6582: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6583: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
6584:
6578:
6579: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6580: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6581:
6582: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6583: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
6584:
6585:
6586: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
6579: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6580: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6581:
6582: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6583: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
6584:
6585:
6586: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
6587: insert into HZ_CUSTOMER_MERGE_LOG (
6623: and m.set_number = set_num;
6624: end if;
6625: -- customer level update --
6626:
6627: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6628: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
6629:
6630: UPDATE oe_blanket_headers_hist a
6631: set sold_to_site_use_id = (select distinct m.customer_id
6624: end if;
6625: -- customer level update --
6626:
6627: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6628: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
6629:
6630: UPDATE oe_blanket_headers_hist a
6631: set sold_to_site_use_id = (select distinct m.customer_id
6632: from ra_customer_merges m
6648: and m.set_number = set_num);
6649:
6650: g_count := sql%rowcount;
6651:
6652: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6653: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6654:
6655: END IF;
6656:
6649:
6650: g_count := sql%rowcount;
6651:
6652: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6653: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6654:
6655: END IF;
6656:
6657: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Hdrs_Hist()-' );
6653: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6654:
6655: END IF;
6656:
6657: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Hdrs_Hist()-' );
6658:
6659: EXCEPTION
6660: When others then
6661: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blkt_Hdrs_Hist-' );
6657: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Hdrs_Hist()-' );
6658:
6659: EXCEPTION
6660: When others then
6661: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blkt_Hdrs_Hist-' );
6662: raise;
6663:
6664: END OE_Merge_Blkt_Hdrs_Hist;
6665:
6803: customer_site_id_tab num_table_binary_int;
6804:
6805: BEGIN
6806:
6807: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Lines()+' );
6808:
6809: IF (process_mode = 'LOCK') THEN
6810:
6811: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6807: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Lines()+' );
6808:
6809: IF (process_mode = 'LOCK') THEN
6810:
6811: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6812: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_all', FALSE );
6813:
6814: open merge_lines;
6815: close merge_lines;
6808:
6809: IF (process_mode = 'LOCK') THEN
6810:
6811: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
6812: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_all', FALSE );
6813:
6814: open merge_lines;
6815: close merge_lines;
6816:
6927:
6928: end if;
6929: end if;
6930:
6931: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6932: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_all', FALSE );
6933:
6934: IF line_line_id_tab.COUNT <> 0 THEN
6935: FORALL i in line_line_id_tab.FIRST..line_line_id_tab.LAST
6928: end if;
6929: end if;
6930:
6931: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
6932: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_all', FALSE );
6933:
6934: IF line_line_id_tab.COUNT <> 0 THEN
6935: FORALL i in line_line_id_tab.FIRST..line_line_id_tab.LAST
6936: UPDATE OE_BLANKET_LINES_ALL L
6954: ELSE
6955: g_count := 0;
6956: END IF;
6957:
6958: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6959: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6960: EXIT WHEN merge_lines_2%NOTFOUND;
6961:
6962: line_line_id_tab.DELETE;
6955: g_count := 0;
6956: END IF;
6957:
6958: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
6959: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
6960: EXIT WHEN merge_lines_2%NOTFOUND;
6961:
6962: line_line_id_tab.DELETE;
6963: line_sold_to_org_id_tab.DELETE;
6978:
6979: END IF;
6980: customer_id_tab.DELETE;
6981: customer_site_id_tab.DELETE;
6982: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Lines()-' );
6983:
6984: EXCEPTION
6985: when others then
6986: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blanket_Lines-' );
6982: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blanket_Lines()-' );
6983:
6984: EXCEPTION
6985: when others then
6986: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blanket_Lines-' );
6987: raise;
6988:
6989: END OE_Merge_Blanket_Lines;
6990:
7138: l_profile_val VARCHAR2(30);
7139:
7140: BEGIN
7141:
7142: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Lines_Hist()+' );
7143:
7144: IF (process_mode = 'LOCK') THEN
7145:
7146: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
7142: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Lines_Hist()+' );
7143:
7144: IF (process_mode = 'LOCK') THEN
7145:
7146: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
7147: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_hist', FALSE );
7148:
7149: open merge_lines_history;
7150: close merge_lines_history;
7143:
7144: IF (process_mode = 'LOCK') THEN
7145:
7146: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
7147: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_hist', FALSE );
7148:
7149: open merge_lines_history;
7150: close merge_lines_history;
7151:
7265:
7266: end if;
7267: end if;
7268:
7269: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7270: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_hist', FALSE );
7271:
7272: IF lhst_line_id_tab.COUNT <> 0 THEN
7273: FORALL i in lhst_line_id_tab.FIRST..lhst_line_id_tab.LAST
7266: end if;
7267: end if;
7268:
7269: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7270: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_lines_hist', FALSE );
7271:
7272: IF lhst_line_id_tab.COUNT <> 0 THEN
7273: FORALL i in lhst_line_id_tab.FIRST..lhst_line_id_tab.LAST
7274: UPDATE OE_BLANKET_LINES_HIST LHIST
7291: ELSE
7292: g_count := 0;
7293: END IF;
7294:
7295: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7296: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7297: EXIT WHEN merge_lines_history_2%NOTFOUND;
7298:
7299: lhst_line_id_tab.DELETE;
7292: g_count := 0;
7293: END IF;
7294:
7295: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7296: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7297: EXIT WHEN merge_lines_history_2%NOTFOUND;
7298:
7299: lhst_line_id_tab.DELETE;
7300: lhst_sold_to_org_id_tab.DELETE;
7316:
7317: END IF;
7318: customer_id_tab.DELETE;
7319: customer_site_id_tab.DELETE;
7320: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Lines_Hist()-' );
7321:
7322: EXCEPTION
7323: when others then
7324: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blkt_Lines_Hist-' );
7320: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_Blkt_Lines_Hist()-' );
7321:
7322: EXCEPTION
7323: when others then
7324: arp_message.set_error( 'OE_CUST_MERGE.OE_Merge_Blkt_Lines_Hist-' );
7325: raise;
7326:
7327: END OE_Merge_Blkt_Lines_Hist;
7328:
7353:
7354: l_profile_val VARCHAR2(30);
7355:
7356: BEGIN
7357: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_OI_Tracking()+' );
7358: IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL < '110510' THEN
7359: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_OI_Tracking()-' );
7360: Return;
7361: END IF;
7355:
7356: BEGIN
7357: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_OI_Tracking()+' );
7358: IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL < '110510' THEN
7359: arp_message.set_line( 'OE_CUST_MERGE.OE_Merge_OI_Tracking()-' );
7360: Return;
7361: END IF;
7362:
7363: -- both customer and site level
7363: -- both customer and site level
7364:
7365: IF( process_mode = 'LOCK' ) THEN
7366:
7367: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
7368: arp_message.set_token( 'TABLE_NAME', 'OE_EM_INFORMATION_ALL', FALSE );
7369:
7370: open c1;
7371: close c1;
7364:
7365: IF( process_mode = 'LOCK' ) THEN
7366:
7367: arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
7368: arp_message.set_token( 'TABLE_NAME', 'OE_EM_INFORMATION_ALL', FALSE );
7369:
7370: open c1;
7371: close c1;
7372:
7377: l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
7378:
7379:
7380: -- customer level update
7381: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7382: arp_message.set_token( 'TABLE_NAME', 'OE_EM_INFORMATION_ALL', FALSE );
7383:
7384: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
7385: insert into HZ_CUSTOMER_MERGE_LOG (
7378:
7379:
7380: -- customer level update
7381: arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
7382: arp_message.set_token( 'TABLE_NAME', 'OE_EM_INFORMATION_ALL', FALSE );
7383:
7384: IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
7385: insert into HZ_CUSTOMER_MERGE_LOG (
7386: MERGE_LOG_ID,
7445:
7446:
7447: g_count := sql%rowcount;
7448:
7449: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7450: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7451:
7452: END IF;
7453:
7446:
7447: g_count := sql%rowcount;
7448:
7449: arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
7450: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7451:
7452: END IF;
7453:
7454: arp_message.set_line( 'OE_CUST_MERGE. OE_Merge_OI_Tracking()-' );
7450: arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
7451:
7452: END IF;
7453:
7454: arp_message.set_line( 'OE_CUST_MERGE. OE_Merge_OI_Tracking()-' );
7455:
7456: EXCEPTION
7457: When others then
7458: arp_message.set_error( 'OE_CUST_MERGE. OE_Merge_OI_Tracking-' );
7454: arp_message.set_line( 'OE_CUST_MERGE. OE_Merge_OI_Tracking()-' );
7455:
7456: EXCEPTION
7457: When others then
7458: arp_message.set_error( 'OE_CUST_MERGE. OE_Merge_OI_Tracking-' );
7459: raise;
7460:
7461: END OE_Merge_OI_Tracking;
7462:
7468: )
7469: IS
7470:
7471: BEGIN
7472: arp_message.set_line( 'OE_CUST_MERGE.OE_Order_Merge()+' );
7473:
7474: OE_Merge_Headers(Req_Id, Set_Num, Process_Mode);
7475: OE_Merge_Lines(Req_Id, Set_Num, Process_Mode);
7476: OE_Merge_Header_History(Req_Id, Set_Num, Process_Mode);
7484: OE_Merge_Blanket_Lines(Req_Id, Set_Num, Process_Mode);
7485: OE_Merge_Blkt_Hdrs_Hist(Req_Id, Set_Num, Process_Mode);
7486: OE_Merge_Blkt_Lines_Hist(Req_Id, Set_Num, Process_Mode);
7487: OE_Merge_OI_Tracking (Req_Id, Set_Num, Process_Mode);
7488: arp_message.set_line( 'calling dbi now...' );
7489: oe_update_dbi_log();
7490: arp_message.set_line( 'OE_CUST_MERGE.Order_Merge()-' );
7491:
7492: EXCEPTION
7486: OE_Merge_Blkt_Lines_Hist(Req_Id, Set_Num, Process_Mode);
7487: OE_Merge_OI_Tracking (Req_Id, Set_Num, Process_Mode);
7488: arp_message.set_line( 'calling dbi now...' );
7489: oe_update_dbi_log();
7490: arp_message.set_line( 'OE_CUST_MERGE.Order_Merge()-' );
7491:
7492: EXCEPTION
7493: When others then
7494: arp_message.set_error( 'OE_CUST_MERGE.OE_Order_Merge-' );
7490: arp_message.set_line( 'OE_CUST_MERGE.Order_Merge()-' );
7491:
7492: EXCEPTION
7493: When others then
7494: arp_message.set_error( 'OE_CUST_MERGE.OE_Order_Merge-' );
7495: raise;
7496:
7497: END OE_Order_Merge;
7498:
7526:
7527: BEGIN
7528:
7529: IF l_dbi_profile = 'Y' then
7530: arp_message.set_line(' inside update_dbi count hder_id_tab='||dbi_header_header_id_tab.COUNT);
7531: arp_message.set_line(' count lin hdr_id_tab='||dbi_line_header_id_tab.COUNT);
7532:
7533:
7534: IF dbi_header_header_id_tab.COUNT <> 0 then
7527: BEGIN
7528:
7529: IF l_dbi_profile = 'Y' then
7530: arp_message.set_line(' inside update_dbi count hder_id_tab='||dbi_header_header_id_tab.COUNT);
7531: arp_message.set_line(' count lin hdr_id_tab='||dbi_line_header_id_tab.COUNT);
7532:
7533:
7534: IF dbi_header_header_id_tab.COUNT <> 0 then
7535: FOR i in dbi_header_header_id_tab.FIRST..dbi_header_header_id_tab.LAST
7533:
7534: IF dbi_header_header_id_tab.COUNT <> 0 then
7535: FOR i in dbi_header_header_id_tab.FIRST..dbi_header_header_id_tab.LAST
7536: LOOP
7537: arp_message.set_line('header.header_id:'||dbi_header_header_id_tab(i));
7538: end loop;
7539: end if;
7540:
7541: IF dbi_line_header_id_tab.COUNT <> 0 then
7540:
7541: IF dbi_line_header_id_tab.COUNT <> 0 then
7542: FOR i in dbi_line_header_id_tab.FIRST..dbi_line_header_id_tab.LAST
7543: LOOP
7544: arp_message.set_line('line.header_id:'||dbi_line_header_id_tab(i));
7545: end loop;
7546: end if;
7547:
7548: IF dbi_line_line_id_tab.COUNT <> 0 then
7547:
7548: IF dbi_line_line_id_tab.COUNT <> 0 then
7549: FOR i in dbi_line_line_id_tab.FIRST..dbi_line_line_id_tab.LAST
7550: LOOP
7551: arp_message.set_line('line.line_id:'||dbi_line_line_id_tab(i));
7552: end loop;
7553: end if;
7554:
7555:
7556:
7557: -- Removing Those Line Ids for whose headers already exists from hdr merge
7558: IF dbi_line_header_id_tab.COUNT <> 0 then
7559:
7560: arp_message.set_line('count line_header_id_tab='||dbi_line_header_id_tab.COUNT);
7561: FOR i in dbi_line_header_id_tab.FIRST..dbi_line_header_id_tab.LAST
7562: LOOP
7563:
7564: arp_message.set_line('count header_id_tab='||dbi_header_id_tab.COUNT);
7560: arp_message.set_line('count line_header_id_tab='||dbi_line_header_id_tab.COUNT);
7561: FOR i in dbi_line_header_id_tab.FIRST..dbi_line_header_id_tab.LAST
7562: LOOP
7563:
7564: arp_message.set_line('count header_id_tab='||dbi_header_id_tab.COUNT);
7565: IF dbi_header_id_tab.EXISTS(dbi_line_header_id_tab(i)) THEN
7566: arp_message.set_line( 'DBI:matched line.header_id and header.header_id of '||dbi_line_header_id_tab(i)||', deleting');
7567: dbi_line_status_tab(i) := 'N';
7568: END IF;
7562: LOOP
7563:
7564: arp_message.set_line('count header_id_tab='||dbi_header_id_tab.COUNT);
7565: IF dbi_header_id_tab.EXISTS(dbi_line_header_id_tab(i)) THEN
7566: arp_message.set_line( 'DBI:matched line.header_id and header.header_id of '||dbi_line_header_id_tab(i)||', deleting');
7567: dbi_line_status_tab(i) := 'N';
7568: END IF;
7569:
7570: END LOOP;
7572:
7573: IF dbi_line_status_tab.COUNT <> 0 then
7574: FOR i in dbi_line_status_tab.FIRST..dbi_line_status_tab.LAST
7575: LOOP
7576: arp_message.set_line( 'status '||i|| '='||dbi_line_status_tab(i));
7577: END LOOP;
7578: END IF;
7579:
7580: OPEN c_set_of_books_id;
7609: WHERE l.header_id = dbi_header_header_id_tab(i)
7610: AND h.header_id = l.header_id
7611: AND h.booked_flag = 'Y';
7612:
7613: arp_message.set_line(' Hdr insert count='||sql%rowcount);
7614:
7615: END IF; -- because of hdr merge
7616:
7617:
7636: FROM OE_ORDER_LINES_ALL l
7637: WHERE l.line_id = dbi_line_line_id_tab(i)
7638: AND l.booked_flag = 'Y'
7639: AND dbi_line_status_tab(i) = 'Y';
7640: arp_message.set_line(' line insert count='||sql%rowcount);
7641:
7642: END IF; -- because of line merge
7643:
7644: END IF; -- if dbi is installed
7652: dbi_line_update_date_tab.DELETE;
7653: dbi_line_status_tab.DELETE;
7654: dbi_header_id_tab.DELETE;
7655:
7656: arp_message.set_line(' End Update DBI');
7657:
7658: EXCEPTION
7659: WHEN OTHERS THEN
7660: oe_debug_pub.add('when others oe_cust_merge.update_dbi_log'||SQLERRM||