[Home] [Help]
64:
65:
66:
67: --
68: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
69: --
70: BEGIN
71: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Headers()+' );
72: IF l_debug_level > 0 THEN
69: --
70: BEGIN
71: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Headers()+' );
72: IF l_debug_level > 0 THEN
73: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADERS' ) ;
74: END IF;
75:
76: /* both customer and site level */
77:
79:
80: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
81: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
82: IF l_debug_level > 0 THEN
83: oe_debug_pub.add( 'LOCKING TABLE OE_ORDER_HEADERS_ALL' ) ;
84: END IF;
85:
86: open c1;
87: close c1;
99: ELSE
100:
101: /* site level update */
102: IF l_debug_level > 0 THEN
103: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
104: END IF;
105: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
106: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
107: IF l_debug_level > 0 THEN
104: END IF;
105: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
106: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
107: IF l_debug_level > 0 THEN
108: oe_debug_pub.add( 'UPDATING OE_ORDER_HEADERS_ALL.SHIP_TO_ORG_ID' ) ;
109: END IF;
110:
111: UPDATE oe_order_headers_all a
112: set ship_to_org_id = (select distinct m.customer_site_id
130: g_count := sql%rowcount;
131: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
132: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
133: IF l_debug_level > 0 THEN
134: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
135: END IF;
136:
137: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
138: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
137: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
138: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
139:
140: IF l_debug_level > 0 THEN
141: oe_debug_pub.add( 'UPDATING OE_ORDER_HEADERS_ALL.INVOICE_TO_ORG_ID' ) ;
142: END IF;
143:
144: UPDATE oe_order_headers_all a
145: set invoice_to_org_id = (select distinct m.customer_site_id
163: g_count := sql%rowcount;
164: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
165: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
166: IF l_debug_level > 0 THEN
167: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
168: END IF;
169:
170: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
171: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
170: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
171: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
172:
173: IF l_debug_level > 0 THEN
174: oe_debug_pub.add( 'UPDATING OE_ORDER_HEADERS_ALL.DELIVER_TO_ORG_ID' ) ;
175: END IF;
176:
177: UPDATE oe_order_headers_all a
178: set deliver_to_org_id = (select distinct m.customer_site_id
196: g_count := sql%rowcount;
197: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
198: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
199: IF l_debug_level > 0 THEN
200: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
201: END IF;
202: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
203: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
204:
204:
205:
206: /* customer level update */
207: IF l_debug_level > 0 THEN
208: oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
209: END IF;
210: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
211: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
212: IF l_debug_level > 0 THEN
209: END IF;
210: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
211: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_headers_all', FALSE );
212: IF l_debug_level > 0 THEN
213: oe_debug_pub.add( 'UPDATING OE_ORDER_HEADERS_ALL.SOLD_TO_ORG_ID' ) ;
214: END IF;
215:
216: UPDATE oe_order_headers_all a
217: set sold_to_org_id = (select distinct m.customer_id
237:
238: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
239: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
240: IF l_debug_level > 0 THEN
241: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
242: END IF;
243:
244:
245: END IF;
245: END IF;
246:
247: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Headers()-' );
248: IF l_debug_level > 0 THEN
249: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADERS' ) ;
250: END IF;
251:
252: EXCEPTION
253: When others then
252: EXCEPTION
253: When others then
254: -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Headers-' );
255: IF l_debug_level > 0 THEN
256: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADERS' ) ;
257: END IF;
258: IF l_debug_level > 0 THEN
259: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
260: END IF;
255: IF l_debug_level > 0 THEN
256: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADERS' ) ;
257: END IF;
258: IF l_debug_level > 0 THEN
259: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
260: END IF;
261: raise;
262:
263: END OE_Merge_Headers;
743:
744:
745:
746: --
747: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
748: --
749: BEGIN
750: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Header_ACKS()+' );
751: IF l_debug_level > 0 THEN
748: --
749: BEGIN
750: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Header_ACKS()+' );
751: IF l_debug_level > 0 THEN
752: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADER_ACKS' ) ;
753: END IF;
754:
755: /* both customer and site level */
756:
755: /* both customer and site level */
756:
757: IF( process_mode = 'LOCK' ) THEN
758: IF l_debug_level > 0 THEN
759: oe_debug_pub.add( 'LOCKING TABLE OE_HEADER_ACKS' ) ;
760: END IF;
761: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
762: -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
763:
777: ELSE
778:
779: /* site level update */
780: IF l_debug_level > 0 THEN
781: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
782: END IF;
783:
784: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
785: -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
783:
784: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
785: -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
786: IF l_debug_level > 0 THEN
787: oe_debug_pub.add( 'UPDATING OE_HEADER_ACKS.SHIP_TO_ORG_ID' ) ;
788: END IF;
789:
790: UPDATE OE_HEADER_ACKS a
791: set ship_to_org_id = (select distinct m.customer_site_id
810: g_count := sql%rowcount;
811: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
812: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
813: IF l_debug_level > 0 THEN
814: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
815: END IF;
816:
817: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
818: -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
816:
817: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
818: -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
819: IF l_debug_level > 0 THEN
820: oe_debug_pub.add( 'UPDATING OE_HEADER_ACKS.INVOICE_TO_ORG_ID' ) ;
821: END IF;
822:
823: UPDATE OE_HEADER_ACKS a
824: set invoice_to_org_id = (select distinct m.customer_site_id
843: g_count := sql%rowcount;
844: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
845: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
846: IF l_debug_level > 0 THEN
847: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
848: END IF;
849:
850: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
851: -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
849:
850: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
851: -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
852: IF l_debug_level > 0 THEN
853: oe_debug_pub.add( 'UPDATING OE_HEADER_ACKS.DELIVER_TO_ORG_ID' ) ;
854: END IF;
855:
856: UPDATE OE_HEADER_ACKS a
857: set deliver_to_org_id = (select distinct m.customer_site_id
876: g_count := sql%rowcount;
877: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
878: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
879: IF l_debug_level > 0 THEN
880: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
881: END IF;
882:
883: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
884: -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
885:
886:
887: /* customer level update */
888: IF l_debug_level > 0 THEN
889: oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
890: END IF;
891: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
892: -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
893:
891: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
892: -- arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
893:
894: IF l_debug_level > 0 THEN
895: oe_debug_pub.add( 'UPDATING OE_HEADER_ACKS.SOLD_TO_ORG_ID' ) ;
896: END IF;
897:
898: UPDATE OE_HEADER_ACKS a
899: set sold_to_org_id = (select distinct m.customer_id
920:
921: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
922: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
923: IF l_debug_level > 0 THEN
924: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
925: END IF;
926:
927: END IF;
928:
927: END IF;
928:
929: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Header_ACKS()-' );
930: IF l_debug_level > 0 THEN
931: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADER_ACKS' ) ;
932: END IF;
933:
934: EXCEPTION
935: When others then
934: EXCEPTION
935: When others then
936: -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Header_ACKS-' );
937: IF l_debug_level > 0 THEN
938: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADER_ACKS' ) ;
939: END IF;
940: IF l_debug_level > 0 THEN
941: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
942: END IF;
937: IF l_debug_level > 0 THEN
938: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_HEADER_ACKS' ) ;
939: END IF;
940: IF l_debug_level > 0 THEN
941: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
942: END IF;
943: raise;
944:
945: END OE_Merge_Header_ACKS;
1010: and m.set_number = set_num)
1011: for update nowait;
1012:
1013: --
1014: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1015: --
1016: BEGIN
1017: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines()+' );
1018: IF l_debug_level > 0 THEN
1015: --
1016: BEGIN
1017: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines()+' );
1018: IF l_debug_level > 0 THEN
1019: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES' ) ;
1020: END IF;
1021:
1022: /* both customer and site level */
1023:
1025:
1026: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1027: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1028: IF l_debug_level > 0 THEN
1029: oe_debug_pub.add( 'LOCKING TABLE OE_ORDER_LINES_ALL' ) ;
1030: END IF;
1031:
1032: open c1;
1033: close c1;
1046:
1047: ELSE
1048: /* site level update */
1049: IF l_debug_level > 0 THEN
1050: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
1051: END IF;
1052:
1053: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1054: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1052:
1053: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1054: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1055: IF l_debug_level > 0 THEN
1056: oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_ALL.SHIP_TO_ORG_ID' ) ;
1057: END IF;
1058:
1059: UPDATE oe_order_lines_all a
1060: set ship_to_org_id = (select distinct m.customer_site_id
1078: g_count := sql%rowcount;
1079: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1080: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1081: IF l_debug_level > 0 THEN
1082: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1083: END IF;
1084: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1085: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1086: IF l_debug_level > 0 THEN
1083: END IF;
1084: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1085: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1086: IF l_debug_level > 0 THEN
1087: oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_ALL.INVOICE_TO_ORG_ID' ) ;
1088: END IF;
1089:
1090: UPDATE oe_order_lines_all a
1091: set invoice_to_org_id = (select distinct m.customer_site_id
1109: g_count := sql%rowcount;
1110: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1111: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1112: IF l_debug_level > 0 THEN
1113: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1114: END IF;
1115:
1116: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1117: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1115:
1116: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1117: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1118: IF l_debug_level > 0 THEN
1119: oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_ALL.DELIVER_TO_ORG_ID' ) ;
1120: END IF;
1121:
1122: UPDATE oe_order_lines_all a
1123: set deliver_to_org_id = (select distinct m.customer_site_id
1141: g_count := sql%rowcount;
1142: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1143: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1144: IF l_debug_level > 0 THEN
1145: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1146: END IF;
1147:
1148: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1149: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1147:
1148: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1149: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1150: IF l_debug_level > 0 THEN
1151: oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_ALL.INTMED_TO_ORG_ID' ) ;
1152: END IF;
1153:
1154: UPDATE oe_order_lines_all a
1155: set intmed_ship_to_org_id = (select distinct m.customer_site_id
1173: g_count := sql%rowcount;
1174: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1175: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1176: IF l_debug_level > 0 THEN
1177: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1178: END IF;
1179:
1180: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1181: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1181: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1182:
1183: /* customer level update */
1184: IF l_debug_level > 0 THEN
1185: oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
1186: END IF;
1187: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1188: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1189: IF l_debug_level > 0 THEN
1186: END IF;
1187: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1188: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_all', FALSE );
1189: IF l_debug_level > 0 THEN
1190: oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_ALL.SOLD_TO_ORG_ID' ) ;
1191: END IF;
1192:
1193: UPDATE oe_order_lines_all a
1194: set sold_to_org_id = (select distinct m.customer_id
1214:
1215: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1216: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1217: IF l_debug_level > 0 THEN
1218: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1219: END IF;
1220:
1221: END IF;
1222:
1221: END IF;
1222:
1223: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines()-' );
1224: IF l_debug_level > 0 THEN
1225: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES' ) ;
1226: END IF;
1227:
1228: EXCEPTION
1229: When others then
1228: EXCEPTION
1229: When others then
1230: -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines-' );
1231: IF l_debug_level > 0 THEN
1232: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES' ) ;
1233: END IF;
1234: IF l_debug_level > 0 THEN
1235: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
1236: END IF;
1231: IF l_debug_level > 0 THEN
1232: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES' ) ;
1233: END IF;
1234: IF l_debug_level > 0 THEN
1235: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
1236: END IF;
1237: raise;
1238:
1239: END OE_Merge_Lines;
1304: and m.set_number = set_num)
1305: for update nowait;
1306:
1307: --
1308: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1309: --
1310: BEGIN
1311: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines_History()+' );
1312: IF l_debug_level > 0 THEN
1309: --
1310: BEGIN
1311: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines_History()+' );
1312: IF l_debug_level > 0 THEN
1313: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES_HISTORY' ) ;
1314: END IF;
1315:
1316: /* both customer and site level */
1317:
1319:
1320: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1321: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1322: IF l_debug_level > 0 THEN
1323: oe_debug_pub.add( 'LOCKING TABLE OE_ORDER_LINES_HISTORY' ) ;
1324: END IF;
1325:
1326: open c1;
1327: close c1;
1340:
1341: ELSE
1342: /* site level update */
1343: IF l_debug_level > 0 THEN
1344: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
1345: END IF;
1346:
1347: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1348: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1346:
1347: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1348: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1349: IF l_debug_level > 0 THEN
1350: oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_HISTORY.SHIP_TO_ORG_ID' ) ;
1351: END IF;
1352:
1353: UPDATE oe_order_lines_history a
1354: set ship_to_org_id = (select distinct m.customer_site_id
1372: g_count := sql%rowcount;
1373: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1374: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1375: IF l_debug_level > 0 THEN
1376: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1377: END IF;
1378:
1379: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1380: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1378:
1379: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1380: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1381: IF l_debug_level > 0 THEN
1382: oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_HISTORY.INVOICE_TO_ORG_ID' ) ;
1383: END IF;
1384:
1385: UPDATE oe_order_lines_history a
1386: set invoice_to_org_id = (select distinct m.customer_site_id
1404: g_count := sql%rowcount;
1405: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1406: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1407: IF l_debug_level > 0 THEN
1408: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1409: END IF;
1410:
1411: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1412: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1410:
1411: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1412: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1413: IF l_debug_level > 0 THEN
1414: oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_HISTORY.DELIVER_TO_ORG_ID' ) ;
1415: END IF;
1416:
1417: UPDATE oe_order_lines_history a
1418: set deliver_to_org_id = (select distinct m.customer_site_id
1436: g_count := sql%rowcount;
1437: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1438: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1439: IF l_debug_level > 0 THEN
1440: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1441: END IF;
1442:
1443: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1444: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1442:
1443: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1444: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1445: IF l_debug_level > 0 THEN
1446: oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_HISTORY.INTMED_TO_ORG_ID' ) ;
1447: END IF;
1448:
1449:
1450: UPDATE oe_order_lines_history a
1469: g_count := sql%rowcount;
1470: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1471: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1472: IF l_debug_level > 0 THEN
1473: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1474: END IF;
1475:
1476: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1477: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1478:
1479:
1480: /* customer level update */
1481: IF l_debug_level > 0 THEN
1482: oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
1483: END IF;
1484: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1485: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1486: IF l_debug_level > 0 THEN
1483: END IF;
1484: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1485: -- arp_message.set_token( 'TABLE_NAME', 'oe_order_lines_history', FALSE );
1486: IF l_debug_level > 0 THEN
1487: oe_debug_pub.add( 'UPDATING OE_ORDER_LINES_HISTORY.SOLD_TO_ORG_ID' ) ;
1488: END IF;
1489:
1490: UPDATE oe_order_lines_history a
1491: set sold_to_org_id = (select distinct m.customer_id
1511:
1512: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1513: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1514: IF l_debug_level > 0 THEN
1515: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1516: END IF;
1517:
1518: END IF;
1519:
1518: END IF;
1519:
1520: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines_History()-' );
1521: IF l_debug_level > 0 THEN
1522: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES_HISTORY' ) ;
1523: END IF;
1524:
1525: EXCEPTION
1526: When others then
1525: EXCEPTION
1526: When others then
1527: -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Lines_History-' );
1528: IF l_debug_level > 0 THEN
1529: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES_HISTORY' ) ;
1530: END IF;
1531: IF l_debug_level > 0 THEN
1532: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
1533: END IF;
1528: IF l_debug_level > 0 THEN
1529: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINES_HISTORY' ) ;
1530: END IF;
1531: IF l_debug_level > 0 THEN
1532: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
1533: END IF;
1534: raise;
1535:
1536: END OE_Merge_Lines_History;
1809: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1810: for update nowait;
1811:
1812: --
1813: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1814: --
1815: BEGIN
1816: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Line_ACKS()+' );
1817: IF l_debug_level > 0 THEN
1814: --
1815: BEGIN
1816: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Line_ACKS()+' );
1817: IF l_debug_level > 0 THEN
1818: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINE_ACKS' ) ;
1819: END IF;
1820:
1821: /* both customer and site level */
1822:
1821: /* both customer and site level */
1822:
1823: IF( process_mode = 'LOCK' ) THEN
1824: IF l_debug_level > 0 THEN
1825: oe_debug_pub.add( 'LOCKING TABLE OE_LINE_ACKS' ) ;
1826: END IF;
1827: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
1828: -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1829:
1844:
1845: ELSE
1846: /* site level update */
1847: IF l_debug_level > 0 THEN
1848: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
1849: END IF;
1850:
1851: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1852: -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1850:
1851: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1852: -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1853: IF l_debug_level > 0 THEN
1854: oe_debug_pub.add( 'UPDATING OE_LINE_ACKS.SHIP_TO_ORG_ID' ) ;
1855: END IF;
1856:
1857: UPDATE oe_line_acks a
1858: set ship_to_org_id = (select distinct m.customer_site_id
1877: g_count := sql%rowcount;
1878: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1879: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1880: IF l_debug_level > 0 THEN
1881: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1882: END IF;
1883: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1884: -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1885: IF l_debug_level > 0 THEN
1882: END IF;
1883: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1884: -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1885: IF l_debug_level > 0 THEN
1886: oe_debug_pub.add( 'UPDATING OE_LINE_ACKS.INVOICE_TO_ORG_ID' ) ;
1887: END IF;
1888:
1889: UPDATE oe_line_acks a
1890: set invoice_to_org_id = (select distinct m.customer_site_id
1909: g_count := sql%rowcount;
1910: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1911: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1912: IF l_debug_level > 0 THEN
1913: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1914: END IF;
1915: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1916: -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1917: IF l_debug_level > 0 THEN
1914: END IF;
1915: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1916: -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1917: IF l_debug_level > 0 THEN
1918: oe_debug_pub.add( 'UPDATING OE_LINE_ACKS.DELIVER_TO_ORG_ID' ) ;
1919: END IF;
1920:
1921: UPDATE oe_line_acks a
1922: set deliver_to_org_id = (select distinct m.customer_site_id
1941: g_count := sql%rowcount;
1942: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1943: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1944: IF l_debug_level > 0 THEN
1945: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1946: END IF;
1947: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1948: -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1949: IF l_debug_level > 0 THEN
1946: END IF;
1947: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1948: -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1949: IF l_debug_level > 0 THEN
1950: oe_debug_pub.add( 'UPDATING OE_LINE_ACKS.INTMED_TO_ORG_ID' ) ;
1951: END IF;
1952:
1953: UPDATE oe_line_acks a
1954: set intmed_ship_to_org_id = (select distinct m.customer_site_id
1973: g_count := sql%rowcount;
1974: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
1975: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
1976: IF l_debug_level > 0 THEN
1977: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
1978: END IF;
1979: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1980: -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1981:
1981:
1982:
1983: /* customer level update */
1984: IF l_debug_level > 0 THEN
1985: oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
1986: END IF;
1987: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1988: -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1989: IF l_debug_level > 0 THEN
1986: END IF;
1987: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
1988: -- arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
1989: IF l_debug_level > 0 THEN
1990: oe_debug_pub.add( 'UPDATING OE_LINE_ACKS.SOLD_TO_ORG_ID' ) ;
1991: END IF;
1992:
1993: UPDATE oe_line_acks a
1994: set sold_to_org_id = (select distinct m.customer_id
2015:
2016: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2017: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2018: IF l_debug_level > 0 THEN
2019: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2020: END IF;
2021:
2022: END IF;
2023:
2022: END IF;
2023:
2024: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Line_ACKS()-' );
2025: IF l_debug_level > 0 THEN
2026: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINE_ACKS' ) ;
2027: END IF;
2028:
2029: EXCEPTION
2030: When others then
2029: EXCEPTION
2030: When others then
2031: -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Merge_Line_ACKS-' );
2032: IF l_debug_level > 0 THEN
2033: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINE_ACKS' ) ;
2034: END IF;
2035: IF l_debug_level > 0 THEN
2036: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
2037: END IF;
2032: IF l_debug_level > 0 THEN
2033: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_MERGE_LINE_ACKS' ) ;
2034: END IF;
2035: IF l_debug_level > 0 THEN
2036: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
2037: END IF;
2038: raise;
2039:
2040: END OE_Merge_Line_ACKS;
2057: Process_Mode IN VARCHAR2)
2058: IS
2059:
2060: --
2061: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2062: --
2063: BEGIN
2064:
2065: /* this part will be calling other internal procedures */
2064:
2065: /* this part will be calling other internal procedures */
2066: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.Merg()+' );
2067: IF l_debug_level > 0 THEN
2068: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.MERGE' ) ;
2069: END IF;
2070:
2071: OE_CUST_MERGE_DATA_FIX.OE_Attachment_Merge (Req_Id, Set_Num, Process_Mode);
2072: OE_CUST_MERGE_DATA_FIX.OE_Defaulting_Merge (Req_Id, Set_Num, Process_Mode);
2078: OE_CUST_MERGE_DATA_FIX.OE_Order_Merge (Req_Id, Set_Num, Process_Mode);
2079:
2080: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.Merg()-' );
2081: IF l_debug_level > 0 THEN
2082: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.MERGE' ) ;
2083: END IF;
2084:
2085: EXCEPTION
2086: When others then
2085: EXCEPTION
2086: When others then
2087: -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.Merg-' );
2088: IF l_debug_level > 0 THEN
2089: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.MERGE' ) ;
2090: END IF;
2091: IF l_debug_level > 0 THEN
2092: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
2093: END IF;
2088: IF l_debug_level > 0 THEN
2089: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.MERGE' ) ;
2090: END IF;
2091: IF l_debug_level > 0 THEN
2092: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
2093: END IF;
2094: raise;
2095:
2096:
2135: for update nowait;
2136:
2137:
2138: --
2139: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2140: --
2141: BEGIN
2142: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Attachment_Merge()+' );
2143: IF l_debug_level > 0 THEN
2140: --
2141: BEGIN
2142: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Attachment_Merge()+' );
2143: IF l_debug_level > 0 THEN
2144: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_ATTACHMENT_MERGE' ) ;
2145: END IF;
2146:
2147: /*-----------------------------+
2148: | OE_ATTACHMENTS_RULE_ELEMENTS|
2150: /* both customer and site level */
2151:
2152: IF( process_mode = 'LOCK' ) THEN
2153: IF l_debug_level > 0 THEN
2154: oe_debug_pub.add( 'LOCKING TABLE OE_ATTACHMENT_RULE_ELEMENTS' ) ;
2155: END IF;
2156: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2157: -- arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
2158:
2170:
2171:
2172: /* site level update */
2173: IF l_debug_level > 0 THEN
2174: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2175: END IF;
2176:
2177: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2178: -- arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
2176:
2177: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2178: -- arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
2179: IF l_debug_level > 0 THEN
2180: oe_debug_pub.add( 'UPDATING OE_ATTACHMENT_RULE_ELEMENTS.ATTRIBUTE_VALUE FOR ATTRIBUTE_CODE SHIP_TO_ORG_ID' ) ;
2181: END IF;
2182: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
2183: set (attribute_value) = (select distinct to_char(m.customer_site_id)
2184: from ra_customer_merges m
2201:
2202: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2203: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2204: IF l_debug_level > 0 THEN
2205: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2206: END IF;
2207:
2208: /* site level update */
2209: IF l_debug_level > 0 THEN
2206: END IF;
2207:
2208: /* site level update */
2209: IF l_debug_level > 0 THEN
2210: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2211: END IF;
2212:
2213: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2214: -- arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
2212:
2213: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2214: -- arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
2215: IF l_debug_level > 0 THEN
2216: oe_debug_pub.add( 'UPDATING OE_ATTACHMENT_RULE_ELEMENTS.ATTRIBUTE_VALUE FOR ATTRIBUTE_CODE INVOICE_TO_ORG_ID' ) ;
2217: END IF;
2218: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
2219: set (attribute_value) = (select distinct to_char(m.customer_site_id)
2220: from ra_customer_merges m
2237:
2238: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2239: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2240: IF l_debug_level > 0 THEN
2241: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2242: END IF;
2243:
2244:
2245: /* customer level update */
2243:
2244:
2245: /* customer level update */
2246: IF l_debug_level > 0 THEN
2247: oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
2248: END IF;
2249: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2250: -- arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
2251: IF l_debug_level > 0 THEN
2248: END IF;
2249: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2250: -- arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
2251: IF l_debug_level > 0 THEN
2252: oe_debug_pub.add( 'UPDATING OE_ATTACHMENT_RULE_ELEMENTS.ATTRIBUTE_VALUE FOR ATTRIBUTE_CODE SOLD_TO_ORG_ID' ) ;
2253: END IF;
2254:
2255: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
2256: set (attribute_value) = (select distinct to_char(m.customer_id)
2275:
2276: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2277: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2278: IF l_debug_level > 0 THEN
2279: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2280: END IF;
2281:
2282: END IF;
2283:
2282: END IF;
2283:
2284: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Attachment_Merge()-' );
2285: IF l_debug_level > 0 THEN
2286: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_ATTACHMENT_MERGE' ) ;
2287: END IF;
2288:
2289:
2290: EXCEPTION
2290: EXCEPTION
2291: when others then
2292: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Attachment_Merge' );
2293: IF l_debug_level > 0 THEN
2294: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_ATTACHMENT_MERGE' ) ;
2295: END IF;
2296: IF l_debug_level > 0 THEN
2297: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
2298: END IF;
2293: IF l_debug_level > 0 THEN
2294: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_ATTACHMENT_MERGE' ) ;
2295: END IF;
2296: IF l_debug_level > 0 THEN
2297: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
2298: END IF;
2299: raise;
2300:
2301:
2394: and attribute_code = 'SOLD_TO_ORG_ID'
2395: for update nowait;
2396:
2397: --
2398: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2399: --
2400: BEGIN
2401: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Defaulting_Merge()+' );
2402: IF l_debug_level > 0 THEN
2399: --
2400: BEGIN
2401: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Defaulting_Merge()+' );
2402: IF l_debug_level > 0 THEN
2403: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_DEFAULTING_MERGE' ) ;
2404: END IF;
2405:
2406: /*-----------------------------+
2407: | OE_DEF_CONDN_ELEMS|
2409: /* both customer and site level */
2410:
2411: IF( process_mode = 'LOCK' ) THEN
2412: IF l_debug_level > 0 THEN
2413: oe_debug_pub.add( 'LOCKING TABLE OE_DEF_CONDN_ELEMENTS' ) ;
2414: END IF;
2415: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2416: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2417:
2445:
2446:
2447: /* site level update */
2448: IF l_debug_level > 0 THEN
2449: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2450: END IF;
2451:
2452: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2453: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2451:
2452: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2453: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2454: IF l_debug_level > 0 THEN
2455: oe_debug_pub.add( 'UPDATING OE_DEF_CONDN_ELEMENTS.VALUE_STRING FOR ATTRIBUTE CODE SHIP_TO_ORG_ID' ) ;
2456: END IF;
2457: UPDATE OE_DEF_CONDN_ELEMS a
2458: set value_string = (select distinct to_char(m.customer_site_id)
2459: from ra_customer_merges m
2477:
2478: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2479: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2480: IF l_debug_level > 0 THEN
2481: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2482: END IF;
2483:
2484: /* site level update */
2485: IF l_debug_level > 0 THEN
2482: END IF;
2483:
2484: /* site level update */
2485: IF l_debug_level > 0 THEN
2486: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2487: END IF;
2488: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2489: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2490: IF l_debug_level > 0 THEN
2487: END IF;
2488: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2489: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2490: IF l_debug_level > 0 THEN
2491: oe_debug_pub.add( 'UPDATING OE_DEF_CONDN_ELEMENTS.VALUE_STRING FOR ATTRIBUTE CODE INVOICE_TO_ORG_ID' ) ;
2492: END IF;
2493: UPDATE OE_DEF_CONDN_ELEMS a
2494: set value_string = (select distinct to_char(m.customer_site_id)
2495: from ra_customer_merges m
2513:
2514: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2515: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2516: IF l_debug_level > 0 THEN
2517: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2518: END IF;
2519:
2520: /* site level update */
2521: IF l_debug_level > 0 THEN
2518: END IF;
2519:
2520: /* site level update */
2521: IF l_debug_level > 0 THEN
2522: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2523: END IF;
2524: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2525: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2526: IF l_debug_level > 0 THEN
2523: END IF;
2524: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2525: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2526: IF l_debug_level > 0 THEN
2527: oe_debug_pub.add( 'UPDATING OE_DEF_CONDN_ELEMENTS.VALUE_STRING FOR ATTRIBUTE CODE INTMED_TO_ORG_ID' ) ;
2528: END IF;
2529: UPDATE OE_DEF_CONDN_ELEMS a
2530: set value_string = (select distinct to_char(m.customer_site_id)
2531: from ra_customer_merges m
2549:
2550: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2551: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2552: IF l_debug_level > 0 THEN
2553: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2554: END IF;
2555:
2556: /* customer level update */
2557: IF l_debug_level > 0 THEN
2554: END IF;
2555:
2556: /* customer level update */
2557: IF l_debug_level > 0 THEN
2558: oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
2559: END IF;
2560: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2561: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2562: IF l_debug_level > 0 THEN
2559: END IF;
2560: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2561: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
2562: IF l_debug_level > 0 THEN
2563: oe_debug_pub.add( 'UPDATING OE_DEF_CONDN_ELEMENTS.VALUE_STRING FOR ATTRIBUTE CODE SOLD_TO_ORG_ID' ) ;
2564: END IF;
2565:
2566: UPDATE OE_DEF_CONDN_ELEMS a
2567: set value_string = (select distinct to_char(m.customer_id)
2587:
2588: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2589: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2590: IF l_debug_level > 0 THEN
2591: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2592: END IF;
2593:
2594: /* site level update */
2595: IF l_debug_level > 0 THEN
2592: END IF;
2593:
2594: /* site level update */
2595: IF l_debug_level > 0 THEN
2596: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2597: END IF;
2598: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2599: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
2600:
2598: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2599: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
2600:
2601: IF l_debug_level > 0 THEN
2602: oe_debug_pub.add( 'UPDATING OE_DEF_ATTR_DEF_RULES.SRC_CONSTANT_VALUE FOR ATTRIBUTE CODE SHIP_TO_ORG_ID' ) ;
2603: END IF;
2604:
2605: UPDATE OE_DEF_ATTR_DEF_RULES a
2606: set src_constant_value = (select distinct to_char(m.customer_site_id)
2625:
2626: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2627: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2628: IF l_debug_level > 0 THEN
2629: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2630: END IF;
2631:
2632: /* site level update */
2633: IF l_debug_level > 0 THEN
2630: END IF;
2631:
2632: /* site level update */
2633: IF l_debug_level > 0 THEN
2634: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2635: END IF;
2636: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2637: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
2638: IF l_debug_level > 0 THEN
2635: END IF;
2636: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2637: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
2638: IF l_debug_level > 0 THEN
2639: oe_debug_pub.add( 'UPDATING OE_DEF_ATTR_DEF_RULES.SRC_CONSTANT_VALUE FOR ATTRIBUTE CODE INVOICE_TO_ORG_ID' ) ;
2640: END IF;
2641:
2642: UPDATE OE_DEF_ATTR_DEF_RULES a
2643: set src_constant_value = (select distinct to_char(m.customer_site_id)
2662:
2663: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2664: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2665: IF l_debug_level > 0 THEN
2666: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2667: END IF;
2668:
2669: /* site level update */
2670: IF l_debug_level > 0 THEN
2667: END IF;
2668:
2669: /* site level update */
2670: IF l_debug_level > 0 THEN
2671: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2672: END IF;
2673: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2674: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
2675: IF l_debug_level > 0 THEN
2672: END IF;
2673: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2674: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
2675: IF l_debug_level > 0 THEN
2676: oe_debug_pub.add( 'UPDATING OE_DEF_ATTR_DEF_RULES.SRC_CONSTANT_VALUE FOR ATTRIBUTE CODE INTMED_TO_ORG_ID' ) ;
2677: END IF;
2678:
2679: UPDATE OE_DEF_ATTR_DEF_RULES a
2680: set src_constant_value = (select distinct to_char(m.customer_site_id)
2699:
2700: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2701: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2702: IF l_debug_level > 0 THEN
2703: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2704: END IF;
2705:
2706: /* customer level update */
2707: IF l_debug_level > 0 THEN
2704: END IF;
2705:
2706: /* customer level update */
2707: IF l_debug_level > 0 THEN
2708: oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
2709: END IF;
2710: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2711: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
2712: IF l_debug_level > 0 THEN
2709: END IF;
2710: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2711: -- arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
2712: IF l_debug_level > 0 THEN
2713: oe_debug_pub.add( 'UPDATING OE_DEF_ATTR_DEF_RULES.SRC_CONSTANT_VALUE FOR ATTRIBUTE CODE SOLD_TO_ORG_ID' ) ;
2714: END IF;
2715:
2716: UPDATE OE_DEF_ATTR_DEF_RULES a
2717: set src_constant_value = (select distinct to_char(m.customer_id)
2737:
2738: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2739: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2740: IF l_debug_level > 0 THEN
2741: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2742: END IF;
2743:
2744:
2745:
2746: END IF;
2747:
2748: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Defaulting_Merge()-' );
2749: IF l_debug_level > 0 THEN
2750: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_DEFAULTING_MERGE' ) ;
2751: END IF;
2752:
2753:
2754: EXCEPTION
2754: EXCEPTION
2755: when others then
2756: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Defaulting_Merge' );
2757: IF l_debug_level > 0 THEN
2758: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_DEFAULTING_MERGE' ) ;
2759: END IF;
2760: IF l_debug_level > 0 THEN
2761: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
2762: END IF;
2757: IF l_debug_level > 0 THEN
2758: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_DEFAULTING_MERGE' ) ;
2759: END IF;
2760: IF l_debug_level > 0 THEN
2761: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
2762: END IF;
2763: raise;
2764:
2765:
2815: for update nowait;
2816:
2817:
2818: --
2819: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2820: --
2821: BEGIN
2822: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.Constraints_Merge()+' );
2823: IF l_debug_level > 0 THEN
2820: --
2821: BEGIN
2822: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.Constraints_Merge()+' );
2823: IF l_debug_level > 0 THEN
2824: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_CONSTRAINTS_MERGE' ) ;
2825: END IF;
2826:
2827: /*-----------------------------+
2828: | oe_pc_vtmplt_cols|
2833:
2834: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
2835: -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2836: IF l_debug_level > 0 THEN
2837: oe_debug_pub.add( 'LOCKING TABLE OE_PC_VTMPLT_COLS' ) ;
2838: END IF;
2839:
2840: open c1;
2841: close c1;
2854:
2855:
2856: /* site level update */
2857: IF l_debug_level > 0 THEN
2858: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2859: END IF;
2860: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2861: -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2862: IF l_debug_level > 0 THEN
2859: END IF;
2860: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2861: -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2862: IF l_debug_level > 0 THEN
2863: oe_debug_pub.add( 'UPDATING OE_PC_VTMPLT_COLS.VALUE_STRING FOR COLUMN_NAME SHIP_TO_ORG_ID' ) ;
2864: END IF;
2865:
2866: UPDATE OE_PC_VTMPLT_COLS a
2867: set value_string = (select distinct to_char(m.customer_site_id)
2886:
2887: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2888: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2889: IF l_debug_level > 0 THEN
2890: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2891: END IF;
2892:
2893: /* site level update */
2894: IF l_debug_level > 0 THEN
2891: END IF;
2892:
2893: /* site level update */
2894: IF l_debug_level > 0 THEN
2895: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2896: END IF;
2897: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2898: -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2899: IF l_debug_level > 0 THEN
2896: END IF;
2897: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2898: -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2899: IF l_debug_level > 0 THEN
2900: oe_debug_pub.add( 'UPDATING OE_PC_VTMPLT_COLS.VALUE_STRING FOR COLUMN_NAME INVOICE_TO_ORG_ID' ) ;
2901: END IF;
2902:
2903: UPDATE OE_PC_VTMPLT_COLS a
2904: set value_string = (select distinct to_char(m.customer_site_id)
2923:
2924: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2925: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2926: IF l_debug_level > 0 THEN
2927: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2928: END IF;
2929:
2930: /* site level update */
2931: IF l_debug_level > 0 THEN
2928: END IF;
2929:
2930: /* site level update */
2931: IF l_debug_level > 0 THEN
2932: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
2933: END IF;
2934: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2935: -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2936: IF l_debug_level > 0 THEN
2933: END IF;
2934: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2935: -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2936: IF l_debug_level > 0 THEN
2937: oe_debug_pub.add( 'UPDATING OE_PC_VTMPLT_COLS.VALUE_STRING FOR COLUMN_NAME INTMED_TO_ORG_ID' ) ;
2938: END IF;
2939:
2940: UPDATE OE_PC_VTMPLT_COLS a
2941: set value_string = (select distinct to_char(m.customer_site_id)
2960:
2961: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
2962: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
2963: IF l_debug_level > 0 THEN
2964: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
2965: END IF;
2966:
2967:
2968: /* customer level update */
2966:
2967:
2968: /* customer level update */
2969: IF l_debug_level > 0 THEN
2970: oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
2971: END IF;
2972: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2973: -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2974: IF l_debug_level > 0 THEN
2971: END IF;
2972: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
2973: -- arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
2974: IF l_debug_level > 0 THEN
2975: oe_debug_pub.add( 'UPDATING OE_PC_VTMPLT_COLS.VALUE_STRING FOR COLUMN_NAME SOLD_TO_ORG_ID' ) ;
2976: END IF;
2977:
2978: UPDATE OE_PC_VTMPLT_COLS a
2979: set value_string = (select distinct to_char(m.customer_id)
2999:
3000: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3001: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3002: IF l_debug_level > 0 THEN
3003: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3004: END IF;
3005:
3006: END IF;
3007:
3006: END IF;
3007:
3008: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Constraints_Merge()-' );
3009: IF l_debug_level > 0 THEN
3010: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_CONSTRAINTS_MERGE' ) ;
3011: END IF;
3012:
3013:
3014: EXCEPTION
3014: EXCEPTION
3015: when others then
3016: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Constraints_Merge' );
3017: IF l_debug_level > 0 THEN
3018: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_CONSTRAINTS_MERGE' ) ;
3019: END IF;
3020: IF l_debug_level > 0 THEN
3021: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3022: END IF;
3017: IF l_debug_level > 0 THEN
3018: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_CONSTRAINTS_MERGE' ) ;
3019: END IF;
3020: IF l_debug_level > 0 THEN
3021: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3022: END IF;
3023: raise;
3024:
3025: END OE_Constraints_Merge;
3067:
3068:
3069:
3070: --
3071: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3072: --
3073: BEGIN
3074:
3075: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Hold_Merge()+' );
3073: BEGIN
3074:
3075: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Hold_Merge()+' );
3076: IF l_debug_level > 0 THEN
3077: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_HOLD_MERGE' ) ;
3078: END IF;
3079: /*-----------------------------+
3080: | OE_HOLD_SOURCES |
3081: +-----------------------------*/
3082: /* both customer and site level */
3083:
3084: IF( process_mode = 'LOCK' ) THEN
3085: IF l_debug_level > 0 THEN
3086: oe_debug_pub.add( 'LOCKING TABLE OE_HOLD_SOURCES' ) ;
3087: END IF;
3088: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3089: -- arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
3090:
3101:
3102:
3103: /* site level update */
3104: IF l_debug_level > 0 THEN
3105: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
3106: END IF;
3107: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3108: -- arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
3109: IF l_debug_level > 0 THEN
3106: END IF;
3107: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3108: -- arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
3109: IF l_debug_level > 0 THEN
3110: oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE S' ) ;
3111: END IF;
3112:
3113: UPDATE OE_HOLD_SOURCES a
3114: set (hold_entity_id) = (select distinct m.customer_site_id
3133:
3134: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3135: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3136: IF l_debug_level > 0 THEN
3137: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3138: END IF;
3139:
3140:
3141: /* site level update */
3139:
3140:
3141: /* site level update */
3142: IF l_debug_level > 0 THEN
3143: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
3144: END IF;
3145: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3146: -- arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
3147: IF l_debug_level > 0 THEN
3144: END IF;
3145: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3146: -- arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
3147: IF l_debug_level > 0 THEN
3148: oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE B' ) ;
3149: END IF;
3150:
3151: UPDATE OE_HOLD_SOURCES a
3152: set (hold_entity_id) = (select distinct m.customer_site_id
3171:
3172: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3173: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3174: IF l_debug_level > 0 THEN
3175: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3176: END IF;
3177:
3178:
3179:
3178:
3179:
3180: /* customer level update */
3181: IF l_debug_level > 0 THEN
3182: oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
3183: END IF;
3184: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3185: -- arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
3186: IF l_debug_level > 0 THEN
3183: END IF;
3184: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3185: -- arp_message.set_token( 'TABLE_NAME', 'SO_HOLD_SOURCES', FALSE );
3186: IF l_debug_level > 0 THEN
3187: oe_debug_pub.add( 'UPDATING OE_HOLD_SOURCES.HOLD_ENTITY_ID FOR HOLD_ENTITY_CODE C' ) ;
3188: END IF;
3189:
3190: UPDATE OE_HOLD_SOURCES a
3191: set hold_entity_id = (select distinct m.customer_id
3209:
3210: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3211: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3212: IF l_debug_level > 0 THEN
3213: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3214: END IF;
3215:
3216: END IF;
3217:
3216: END IF;
3217:
3218: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Hold_Merge()-' );
3219: IF l_debug_level > 0 THEN
3220: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_HOLD_MERGE' ) ;
3221: END IF;
3222:
3223:
3224: EXCEPTION
3224: EXCEPTION
3225: when others then
3226: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Hold_Merge' );
3227: IF l_debug_level > 0 THEN
3228: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_HOLD_MERGE' ) ;
3229: END IF;
3230: IF l_debug_level > 0 THEN
3231: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3232: END IF;
3227: IF l_debug_level > 0 THEN
3228: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_HOLD_MERGE' ) ;
3229: END IF;
3230: IF l_debug_level > 0 THEN
3231: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3232: END IF;
3233: raise;
3234:
3235: END OE_Hold_Merge;
3250: for update nowait;
3251:
3252:
3253: --
3254: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3255: --
3256: BEGIN
3257: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Drop_SHip_Merge()+' );
3258: IF l_debug_level > 0 THEN
3255: --
3256: BEGIN
3257: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Drop_SHip_Merge()+' );
3258: IF l_debug_level > 0 THEN
3259: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_DROP_SHIP_MERGE' ) ;
3260: END IF;
3261:
3262: /* both customer and site level */
3263:
3265:
3266: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3267: -- arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
3268: IF l_debug_level > 0 THEN
3269: oe_debug_pub.add( 'LOCKING TABLE OE_DROP_SHIP_SOURCES' ) ;
3270: END IF;
3271:
3272: open c1;
3273: close c1;
3275: ELSE
3276:
3277: /* site level update */
3278: IF l_debug_level > 0 THEN
3279: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
3280: END IF;
3281: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3282: -- arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
3283: IF l_debug_level > 0 THEN
3280: END IF;
3281: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3282: -- arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
3283: IF l_debug_level > 0 THEN
3284: oe_debug_pub.add( 'UPDATING OE_DROP_SHIP_SOURCES.LINE_LOCATION_ID' ) ;
3285: END IF;
3286:
3287: UPDATE OE_DROP_SHIP_SOURCES a
3288: set line_location_id = (select distinct m.customer_site_id
3302: g_count := sql%rowcount;
3303: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3304: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3305: IF l_debug_level > 0 THEN
3306: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3307: END IF;
3308:
3309: END IF;
3310:
3309: END IF;
3310:
3311: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Drop_SHip_Merge()-' );
3312: IF l_debug_level > 0 THEN
3313: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_DROP_SHIP_MERGE' ) ;
3314: END IF;
3315:
3316: EXCEPTION
3317: When others then
3316: EXCEPTION
3317: When others then
3318: -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Drop_SHip_Merge-' );
3319: IF l_debug_level > 0 THEN
3320: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_DROP_SHIP_MERGE' ) ;
3321: END IF;
3322: IF l_debug_level > 0 THEN
3323: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3324: END IF;
3319: IF l_debug_level > 0 THEN
3320: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_DROP_SHIP_MERGE' ) ;
3321: END IF;
3322: IF l_debug_level > 0 THEN
3323: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3324: END IF;
3325: raise;
3326:
3327: END OE_Drop_Ship_Merge;
3354:
3355:
3356:
3357: --
3358: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3359: --
3360: BEGIN
3361: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_ship_tolerance_merge()+' );
3362: IF l_debug_level > 0 THEN
3359: --
3360: BEGIN
3361: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_ship_tolerance_merge()+' );
3362: IF l_debug_level > 0 THEN
3363: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_SHIP_TOLERANCE_MERGE' ) ;
3364: END IF;
3365:
3366: /* both customer and site level */
3367:
3369:
3370: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3371: -- arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
3372: IF l_debug_level > 0 THEN
3373: oe_debug_pub.add( 'LOCKING TABLE OE_CUST_ITEM_SETTINGS' ) ;
3374: END IF;
3375:
3376: open c1;
3377: close c1;
3384: ELSE
3385:
3386: /* site level update */
3387: IF l_debug_level > 0 THEN
3388: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
3389: END IF;
3390: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3391: -- arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
3392: IF l_debug_level > 0 THEN
3389: END IF;
3390: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3391: -- arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
3392: IF l_debug_level > 0 THEN
3393: oe_debug_pub.add( 'UPDATING OE_CUST_ITEM_SETTINGS.SITE_USE_ID' ) ;
3394: END IF;
3395:
3396: UPDATE OE_CUST_ITEM_SETTINGS a
3397: set site_use_id = (select distinct m.customer_site_id
3411: g_count := sql%rowcount;
3412: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3413: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3414: IF l_debug_level > 0 THEN
3415: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3416: END IF;
3417:
3418: /* customer level update */
3419: IF l_debug_level > 0 THEN
3416: END IF;
3417:
3418: /* customer level update */
3419: IF l_debug_level > 0 THEN
3420: oe_debug_pub.add( 'CUSTOMER LEVEL UPDATE' ) ;
3421: END IF;
3422: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3423: -- arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
3424: IF l_debug_level > 0 THEN
3421: END IF;
3422: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3423: -- arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
3424: IF l_debug_level > 0 THEN
3425: oe_debug_pub.add( 'UPDATING OE_CUST_ITEM_SETTINGS.CUSTOMER_ID' ) ;
3426: END IF;
3427:
3428: UPDATE OE_CUST_ITEM_SETTINGS a
3429: set customer_id = (select distinct m.customer_id
3443: g_count := sql%rowcount;
3444: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3445: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3446: IF l_debug_level > 0 THEN
3447: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3448: END IF;
3449:
3450: END IF;
3451:
3450: END IF;
3451:
3452: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Ship_Tolerance_Merge()-' );
3453: IF l_debug_level > 0 THEN
3454: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_SHIP_TOLERANCE_MERGE' ) ;
3455: END IF;
3456:
3457:
3458:
3459: EXCEPTION
3460: When others then
3461: -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Ship_Tolerance_Merge-' );
3462: IF l_debug_level > 0 THEN
3463: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_SHIP_TOLERANCE_MERGE' ) ;
3464: END IF;
3465: IF l_debug_level > 0 THEN
3466: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3467: END IF;
3462: IF l_debug_level > 0 THEN
3463: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_SHIP_TOLERANCE_MERGE' ) ;
3464: END IF;
3465: IF l_debug_level > 0 THEN
3466: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3467: END IF;
3468: raise;
3469:
3470: END OE_Ship_Tolerance_Merge;
3486: for update nowait;
3487:
3488:
3489: --
3490: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3491: --
3492: BEGIN
3493: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Sets_Merge()+' );
3494: IF l_debug_level > 0 THEN
3491: --
3492: BEGIN
3493: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Sets_Merge()+' );
3494: IF l_debug_level > 0 THEN
3495: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_SETS_MERGE' ) ;
3496: END IF;
3497:
3498: /* both customer and site level */
3499:
3501:
3502: -- arp_message.set_name( 'AR', 'AR_LOCKING_TABLE');
3503: -- arp_message.set_token( 'TABLE_NAME', 'OE_SETS', FALSE );
3504: IF l_debug_level > 0 THEN
3505: oe_debug_pub.add( 'LOCKING TABLE OE_SETS' ) ;
3506: END IF;
3507:
3508: open c1;
3509: close c1;
3511: ELSE
3512:
3513: /* site level update */
3514: IF l_debug_level > 0 THEN
3515: oe_debug_pub.add( 'SITE LEVEL UPDATE' ) ;
3516: END IF;
3517: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3518: -- arp_message.set_token( 'TABLE_NAME', 'OE_SETS', FALSE );
3519: IF l_debug_level > 0 THEN
3516: END IF;
3517: -- arp_message.set_name( 'AR', 'AR_UPDATING_TABLE');
3518: -- arp_message.set_token( 'TABLE_NAME', 'OE_SETS', FALSE );
3519: IF l_debug_level > 0 THEN
3520: oe_debug_pub.add( 'UPDATING OE_SETS.SHIP_TO_ORG_ID' ) ;
3521: END IF;
3522:
3523: UPDATE oe_sets a
3524: set ship_to_org_id = (select distinct m.customer_site_id
3539:
3540: -- arp_message.set_name( 'AR', 'AR_ROWS_UPDATED' );
3541: -- arp_message.set_token( 'NUM_ROWS', to_char(g_count) );
3542: IF l_debug_level > 0 THEN
3543: oe_debug_pub.add( G_COUNT || ' ROWS UPDATED' ) ;
3544: END IF;
3545:
3546: END IF;
3547:
3546: END IF;
3547:
3548: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Sets_Merge()-' );
3549: IF l_debug_level > 0 THEN
3550: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_SETS_MERGE' ) ;
3551: END IF;
3552:
3553: EXCEPTION
3554: when others then
3553: EXCEPTION
3554: when others then
3555: -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Sets_Merge-' );
3556: IF l_debug_level > 0 THEN
3557: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_SETS_MERGE' ) ;
3558: END IF;
3559: IF l_debug_level > 0 THEN
3560: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3561: END IF;
3556: IF l_debug_level > 0 THEN
3557: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_SETS_MERGE' ) ;
3558: END IF;
3559: IF l_debug_level > 0 THEN
3560: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3561: END IF;
3562:
3563: raise;
3564:
3571: Process_Mode IN VARCHAR2)
3572: IS
3573:
3574: --
3575: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3576: --
3577: BEGIN
3578: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Order_Merge()+' );
3579: IF l_debug_level > 0 THEN
3576: --
3577: BEGIN
3578: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.OE_Order_Merge()+' );
3579: IF l_debug_level > 0 THEN
3580: oe_debug_pub.add( 'BEGIN OE_CUST_MERGE_DATA_FIX.OE_ORDER_MERGE' ) ;
3581: END IF;
3582:
3583: OE_Merge_Headers(Req_Id, Set_Num, Process_Mode);
3584: OE_Merge_Lines(Req_Id, Set_Num, Process_Mode);
3590: OE_Merge_Line_ACKS(Req_Id, Set_Num, Process_Mode);
3591:
3592: -- arp_message.set_line( 'OE_CUST_MERGE_DATA_FIX.Order_Merge()-' );
3593: IF l_debug_level > 0 THEN
3594: oe_debug_pub.add( 'END OE_CUST_MERGE_DATA_FIX.OE_ORDER_MERGE' ) ;
3595: END IF;
3596:
3597: EXCEPTION
3598: When others then
3597: EXCEPTION
3598: When others then
3599: -- arp_message.set_error( 'OE_CUST_MERGE_DATA_FIX.OE_Order_Merge-' );
3600: IF l_debug_level > 0 THEN
3601: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_ORDER_MERGE' ) ;
3602: END IF;
3603: IF l_debug_level > 0 THEN
3604: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3605: END IF;
3600: IF l_debug_level > 0 THEN
3601: oe_debug_pub.add( 'ERROR IN OE_CUST_MERGE_DATA_FIX.OE_ORDER_MERGE' ) ;
3602: END IF;
3603: IF l_debug_level > 0 THEN
3604: oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 2000 ) ) ;
3605: END IF;
3606:
3607: raise;
3608:
3614: Process_Mode IN VARCHAR2)
3615: IS
3616:
3617: --
3618: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3619: --
3620: BEGIN
3621:
3622: NULL;