[Home] [Help]
61: IS
62:
63: CURSOR MERGE_SITES IS
64: select duplicate_id, customer_id, duplicate_site_id, customer_site_id
65: from ra_customer_merges m
66: where m.process_flag = 'N'
67: and m.request_id = req_id
68: and m.set_number = set_num;
69:
78: H.END_CUSTOMER_ID, 'N' ,
79: NVL(M1.CUSTOMER_MERGE_ID,NVL(M2.CUSTOMER_MERGE_ID,NVL(M3.CUSTOMER_MERGE_ID,
80: NVL(M4.CUSTOMER_MERGE_ID,nvl(M5.CUSTOMER_MERGE_ID,
81: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
82: FROM RA_CUSTOMER_MERGES M1,
83: RA_CUSTOMER_MERGES M2,
84: RA_CUSTOMER_MERGES M3,
85: RA_CUSTOMER_MERGES M4,
86: RA_CUSTOMER_MERGES M5,
79: NVL(M1.CUSTOMER_MERGE_ID,NVL(M2.CUSTOMER_MERGE_ID,NVL(M3.CUSTOMER_MERGE_ID,
80: NVL(M4.CUSTOMER_MERGE_ID,nvl(M5.CUSTOMER_MERGE_ID,
81: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
82: FROM RA_CUSTOMER_MERGES M1,
83: RA_CUSTOMER_MERGES M2,
84: RA_CUSTOMER_MERGES M3,
85: RA_CUSTOMER_MERGES M4,
86: RA_CUSTOMER_MERGES M5,
87: RA_CUSTOMER_MERGES M6,
80: NVL(M4.CUSTOMER_MERGE_ID,nvl(M5.CUSTOMER_MERGE_ID,
81: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
82: FROM RA_CUSTOMER_MERGES M1,
83: RA_CUSTOMER_MERGES M2,
84: RA_CUSTOMER_MERGES M3,
85: RA_CUSTOMER_MERGES M4,
86: RA_CUSTOMER_MERGES M5,
87: RA_CUSTOMER_MERGES M6,
88: RA_CUSTOMER_MERGES M7,
81: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
82: FROM RA_CUSTOMER_MERGES M1,
83: RA_CUSTOMER_MERGES M2,
84: RA_CUSTOMER_MERGES M3,
85: RA_CUSTOMER_MERGES M4,
86: RA_CUSTOMER_MERGES M5,
87: RA_CUSTOMER_MERGES M6,
88: RA_CUSTOMER_MERGES M7,
89: OE_ORDER_HEADERS H
82: FROM RA_CUSTOMER_MERGES M1,
83: RA_CUSTOMER_MERGES M2,
84: RA_CUSTOMER_MERGES M3,
85: RA_CUSTOMER_MERGES M4,
86: RA_CUSTOMER_MERGES M5,
87: RA_CUSTOMER_MERGES M6,
88: RA_CUSTOMER_MERGES M7,
89: OE_ORDER_HEADERS H
90: WHERE ( H.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
83: RA_CUSTOMER_MERGES M2,
84: RA_CUSTOMER_MERGES M3,
85: RA_CUSTOMER_MERGES M4,
86: RA_CUSTOMER_MERGES M5,
87: RA_CUSTOMER_MERGES M6,
88: RA_CUSTOMER_MERGES M7,
89: OE_ORDER_HEADERS H
90: WHERE ( H.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
91: AND H.INVOICE_TO_ORG_ID = M2.DUPLICATE_SITE_ID(+)
84: RA_CUSTOMER_MERGES M3,
85: RA_CUSTOMER_MERGES M4,
86: RA_CUSTOMER_MERGES M5,
87: RA_CUSTOMER_MERGES M6,
88: RA_CUSTOMER_MERGES M7,
89: OE_ORDER_HEADERS H
90: WHERE ( H.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
91: AND H.INVOICE_TO_ORG_ID = M2.DUPLICATE_SITE_ID(+)
92: AND H.SHIP_TO_ORG_ID = M3.DUPLICATE_SITE_ID(+)
135: H.END_CUSTOMER_ID,'N',
136: NVL(M1.CUSTOMER_MERGE_ID,NVL(M2.CUSTOMER_MERGE_ID,NVL(M3.CUSTOMER_MERGE_ID,
137: NVL(M4.CUSTOMER_MERGE_ID,nvl(M5.CUSTOMER_MERGE_ID,
138: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
139: FROM RA_CUSTOMER_MERGES M1,
140: RA_CUSTOMER_MERGES M2,
141: RA_CUSTOMER_MERGES M3,
142: RA_CUSTOMER_MERGES M4,
143: RA_CUSTOMER_MERGES M5,
136: NVL(M1.CUSTOMER_MERGE_ID,NVL(M2.CUSTOMER_MERGE_ID,NVL(M3.CUSTOMER_MERGE_ID,
137: NVL(M4.CUSTOMER_MERGE_ID,nvl(M5.CUSTOMER_MERGE_ID,
138: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
139: FROM RA_CUSTOMER_MERGES M1,
140: RA_CUSTOMER_MERGES M2,
141: RA_CUSTOMER_MERGES M3,
142: RA_CUSTOMER_MERGES M4,
143: RA_CUSTOMER_MERGES M5,
144: RA_CUSTOMER_MERGES M6,
137: NVL(M4.CUSTOMER_MERGE_ID,nvl(M5.CUSTOMER_MERGE_ID,
138: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
139: FROM RA_CUSTOMER_MERGES M1,
140: RA_CUSTOMER_MERGES M2,
141: RA_CUSTOMER_MERGES M3,
142: RA_CUSTOMER_MERGES M4,
143: RA_CUSTOMER_MERGES M5,
144: RA_CUSTOMER_MERGES M6,
145: RA_CUSTOMER_MERGES M7,
138: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
139: FROM RA_CUSTOMER_MERGES M1,
140: RA_CUSTOMER_MERGES M2,
141: RA_CUSTOMER_MERGES M3,
142: RA_CUSTOMER_MERGES M4,
143: RA_CUSTOMER_MERGES M5,
144: RA_CUSTOMER_MERGES M6,
145: RA_CUSTOMER_MERGES M7,
146: OE_ORDER_HEADERS H
139: FROM RA_CUSTOMER_MERGES M1,
140: RA_CUSTOMER_MERGES M2,
141: RA_CUSTOMER_MERGES M3,
142: RA_CUSTOMER_MERGES M4,
143: RA_CUSTOMER_MERGES M5,
144: RA_CUSTOMER_MERGES M6,
145: RA_CUSTOMER_MERGES M7,
146: OE_ORDER_HEADERS H
147: WHERE ( H.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
140: RA_CUSTOMER_MERGES M2,
141: RA_CUSTOMER_MERGES M3,
142: RA_CUSTOMER_MERGES M4,
143: RA_CUSTOMER_MERGES M5,
144: RA_CUSTOMER_MERGES M6,
145: RA_CUSTOMER_MERGES M7,
146: OE_ORDER_HEADERS H
147: WHERE ( H.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
148: AND H.INVOICE_TO_ORG_ID = M2.DUPLICATE_SITE_ID(+)
141: RA_CUSTOMER_MERGES M3,
142: RA_CUSTOMER_MERGES M4,
143: RA_CUSTOMER_MERGES M5,
144: RA_CUSTOMER_MERGES M6,
145: RA_CUSTOMER_MERGES M7,
146: OE_ORDER_HEADERS H
147: WHERE ( H.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
148: AND H.INVOICE_TO_ORG_ID = M2.DUPLICATE_SITE_ID(+)
149: AND H.SHIP_TO_ORG_ID = M3.DUPLICATE_SITE_ID(+)
199: H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
200: H.SOLD_TO_SITE_USE_ID, H.END_CUSTOMER_SITE_USE_ID,
201: H.END_CUSTOMER_ID,'N',M1.CUSTOMER_MERGE_ID
202: FROM
203: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS H
204: WHERE H.INVOICE_TO_ORG_ID= M1.DUPLICATE_SITE_ID
205: AND M1.PROCESS_FLAG='N'
206: AND M1.REQUEST_ID = req_id
207: AND M1.SET_NUMBER = set_num
211: H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
212: H.SOLD_TO_SITE_USE_ID, H.END_CUSTOMER_SITE_USE_ID,
213: H.END_CUSTOMER_ID,'N',M1.CUSTOMER_MERGE_ID
214: FROM
215: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS H
216: WHERE H.SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
217: AND M1.PROCESS_FLAG='N'
218: AND M1.REQUEST_ID = req_id
219: AND M1.SET_NUMBER = set_num
223: H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
224: H.SOLD_TO_SITE_USE_ID, H.END_CUSTOMER_SITE_USE_ID,
225: H.END_CUSTOMER_ID,'N',M1.CUSTOMER_MERGE_ID
226: FROM
227: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS H
228: WHERE H.SOLD_TO_SITE_USE_ID= M1.DUPLICATE_SITE_ID
229: AND M1.PROCESS_FLAG='N'
230: AND M1.REQUEST_ID = req_id
231: AND M1.SET_NUMBER = set_num
235: H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
236: H.SOLD_TO_SITE_USE_ID, H.END_CUSTOMER_SITE_USE_ID,
237: H.END_CUSTOMER_ID,'N',M1.CUSTOMER_MERGE_ID
238: FROM
239: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS H
240: WHERE H.DELIVER_TO_ORG_ID= M1.DUPLICATE_SITE_ID
241: AND M1.PROCESS_FLAG='N'
242: AND M1.REQUEST_ID = req_id
243: AND M1.SET_NUMBER = set_num
247: H.INVOICE_TO_ORG_ID, H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
248: H.SOLD_TO_SITE_USE_ID, H.END_CUSTOMER_SITE_USE_ID,
249: H.END_CUSTOMER_ID,'N',M1.CUSTOMER_MERGE_ID
250: FROM
251: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS H
252: WHERE H.END_CUSTOMER_SITE_USE_ID= M1.DUPLICATE_SITE_ID
253: AND M1.PROCESS_FLAG='N'
254: AND M1.REQUEST_ID = req_id
255: AND M1.SET_NUMBER = set_num
335: IF l_run_parallel_query = 'N' THEN
336: ----bug 8883694,9349882 Inserting unique duplicate_id into GTT for a request .
337: INSERT INTO OE_CUST_MERGES_GTT(duplicate_id,request_id,set_number,process_flag,customer_merge_id)
338: SELECT duplicate_id,request_id,set_number,process_flag, customer_merge_id
339: FROM RA_CUSTOMER_MERGES M1
340: WHERE M1.PROCESS_FLAG='N'
341: AND M1.REQUEST_ID = req_id
342: AND M1.SET_NUMBER = set_num
343: and m1.customer_merge_id=(select min(m2.customer_merge_id)
340: WHERE M1.PROCESS_FLAG='N'
341: AND M1.REQUEST_ID = req_id
342: AND M1.SET_NUMBER = set_num
343: and m1.customer_merge_id=(select min(m2.customer_merge_id)
344: from ra_customer_merges m2
345: where M2.PROCESS_FLAG='N'
346: AND M2.REQUEST_ID = m1.request_id
347: AND M2.SET_NUMBER = m1.SET_NUMBER
348: and M2.DUPLICATE_ID = M1.DUPLICATE_ID
631: IS
632: --3196900
633: CURSOR MERGE_SITES IS
634: select duplicate_id, customer_id, duplicate_site_id, customer_site_id
635: from ra_customer_merges m
636: where m.process_flag = 'N'
637: and m.request_id = req_id
638: and m.set_number = set_num;
639: /* MOAC_SQL_CHANGE */
653: hist.sold_to_site_use_id,
654: hist.deliver_to_org_id,
655: hist.end_customer_site_use_id,
656: hist.end_customer_id
657: FROM RA_CUSTOMER_MERGES M1,
658: RA_CUSTOMER_MERGES M2,
659: RA_CUSTOMER_MERGES M3,
660: RA_CUSTOMER_MERGES M4,
661: RA_CUSTOMER_MERGES M5,
654: hist.deliver_to_org_id,
655: hist.end_customer_site_use_id,
656: hist.end_customer_id
657: FROM RA_CUSTOMER_MERGES M1,
658: RA_CUSTOMER_MERGES M2,
659: RA_CUSTOMER_MERGES M3,
660: RA_CUSTOMER_MERGES M4,
661: RA_CUSTOMER_MERGES M5,
662: RA_CUSTOMER_MERGES M6,
655: hist.end_customer_site_use_id,
656: hist.end_customer_id
657: FROM RA_CUSTOMER_MERGES M1,
658: RA_CUSTOMER_MERGES M2,
659: RA_CUSTOMER_MERGES M3,
660: RA_CUSTOMER_MERGES M4,
661: RA_CUSTOMER_MERGES M5,
662: RA_CUSTOMER_MERGES M6,
663: RA_CUSTOMER_MERGES M7,
656: hist.end_customer_id
657: FROM RA_CUSTOMER_MERGES M1,
658: RA_CUSTOMER_MERGES M2,
659: RA_CUSTOMER_MERGES M3,
660: RA_CUSTOMER_MERGES M4,
661: RA_CUSTOMER_MERGES M5,
662: RA_CUSTOMER_MERGES M6,
663: RA_CUSTOMER_MERGES M7,
664: OE_ORDER_HEADER_HISTORY HIST,
657: FROM RA_CUSTOMER_MERGES M1,
658: RA_CUSTOMER_MERGES M2,
659: RA_CUSTOMER_MERGES M3,
660: RA_CUSTOMER_MERGES M4,
661: RA_CUSTOMER_MERGES M5,
662: RA_CUSTOMER_MERGES M6,
663: RA_CUSTOMER_MERGES M7,
664: OE_ORDER_HEADER_HISTORY HIST,
665: -- Changed by Srini for MOAC
658: RA_CUSTOMER_MERGES M2,
659: RA_CUSTOMER_MERGES M3,
660: RA_CUSTOMER_MERGES M4,
661: RA_CUSTOMER_MERGES M5,
662: RA_CUSTOMER_MERGES M6,
663: RA_CUSTOMER_MERGES M7,
664: OE_ORDER_HEADER_HISTORY HIST,
665: -- Changed by Srini for MOAC
666: -- This MOAC change has been reverted.
659: RA_CUSTOMER_MERGES M3,
660: RA_CUSTOMER_MERGES M4,
661: RA_CUSTOMER_MERGES M5,
662: RA_CUSTOMER_MERGES M6,
663: RA_CUSTOMER_MERGES M7,
664: OE_ORDER_HEADER_HISTORY HIST,
665: -- Changed by Srini for MOAC
666: -- This MOAC change has been reverted.
667: -- For more information please
725: hist.sold_to_site_use_id,
726: hist.deliver_to_org_id,
727: hist.end_customer_site_use_id,
728: hist.end_customer_id
729: FROM RA_CUSTOMER_MERGES M1,
730: RA_CUSTOMER_MERGES M2,
731: RA_CUSTOMER_MERGES M3,
732: RA_CUSTOMER_MERGES M4,
733: RA_CUSTOMER_MERGES M5,
726: hist.deliver_to_org_id,
727: hist.end_customer_site_use_id,
728: hist.end_customer_id
729: FROM RA_CUSTOMER_MERGES M1,
730: RA_CUSTOMER_MERGES M2,
731: RA_CUSTOMER_MERGES M3,
732: RA_CUSTOMER_MERGES M4,
733: RA_CUSTOMER_MERGES M5,
734: RA_CUSTOMER_MERGES M6,
727: hist.end_customer_site_use_id,
728: hist.end_customer_id
729: FROM RA_CUSTOMER_MERGES M1,
730: RA_CUSTOMER_MERGES M2,
731: RA_CUSTOMER_MERGES M3,
732: RA_CUSTOMER_MERGES M4,
733: RA_CUSTOMER_MERGES M5,
734: RA_CUSTOMER_MERGES M6,
735: RA_CUSTOMER_MERGES M7,
728: hist.end_customer_id
729: FROM RA_CUSTOMER_MERGES M1,
730: RA_CUSTOMER_MERGES M2,
731: RA_CUSTOMER_MERGES M3,
732: RA_CUSTOMER_MERGES M4,
733: RA_CUSTOMER_MERGES M5,
734: RA_CUSTOMER_MERGES M6,
735: RA_CUSTOMER_MERGES M7,
736: OE_ORDER_HEADER_HISTORY HIST,
729: FROM RA_CUSTOMER_MERGES M1,
730: RA_CUSTOMER_MERGES M2,
731: RA_CUSTOMER_MERGES M3,
732: RA_CUSTOMER_MERGES M4,
733: RA_CUSTOMER_MERGES M5,
734: RA_CUSTOMER_MERGES M6,
735: RA_CUSTOMER_MERGES M7,
736: OE_ORDER_HEADER_HISTORY HIST,
737: --Changed for MOAC
730: RA_CUSTOMER_MERGES M2,
731: RA_CUSTOMER_MERGES M3,
732: RA_CUSTOMER_MERGES M4,
733: RA_CUSTOMER_MERGES M5,
734: RA_CUSTOMER_MERGES M6,
735: RA_CUSTOMER_MERGES M7,
736: OE_ORDER_HEADER_HISTORY HIST,
737: --Changed for MOAC
738: OE_ORDER_HEADERS_ALL H
731: RA_CUSTOMER_MERGES M3,
732: RA_CUSTOMER_MERGES M4,
733: RA_CUSTOMER_MERGES M5,
734: RA_CUSTOMER_MERGES M6,
735: RA_CUSTOMER_MERGES M7,
736: OE_ORDER_HEADER_HISTORY HIST,
737: --Changed for MOAC
738: OE_ORDER_HEADERS_ALL H
739: WHERE
793: HIST.HEADER_ID , HIST.SOLD_TO_ORG_ID, HIST.INVOICE_TO_ORG_ID,
794: HIST.SHIP_TO_ORG_ID, HIST.SOLD_TO_SITE_USE_ID, HIST.DELIVER_TO_ORG_ID,
795: HIST.END_CUSTOMER_SITE_USE_ID, HIST.END_CUSTOMER_ID
796: FROM
797: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS_ALL H, OE_ORDER_HEADER_HISTORY HIST
798: WHERE HIST.INVOICE_TO_ORG_ID= M1.DUPLICATE_SITE_ID
799: AND M1.PROCESS_FLAG='N'
800: AND M1.REQUEST_ID = req_id
801: AND M1.SET_NUMBER = set_num
805: HIST.HEADER_ID , HIST.SOLD_TO_ORG_ID, HIST.INVOICE_TO_ORG_ID,
806: HIST.SHIP_TO_ORG_ID, HIST.SOLD_TO_SITE_USE_ID, HIST.DELIVER_TO_ORG_ID,
807: HIST.END_CUSTOMER_SITE_USE_ID, HIST.END_CUSTOMER_ID
808: FROM
809: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS_ALL H, OE_ORDER_HEADER_HISTORY HIST
810: WHERE HIST.SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
811: AND M1.PROCESS_FLAG='N'
812: AND M1.REQUEST_ID = req_id
813: AND M1.SET_NUMBER = set_num
817: HIST.HEADER_ID , HIST.SOLD_TO_ORG_ID, HIST.INVOICE_TO_ORG_ID,
818: HIST.SHIP_TO_ORG_ID, HIST.SOLD_TO_SITE_USE_ID, HIST.DELIVER_TO_ORG_ID,
819: HIST.END_CUSTOMER_SITE_USE_ID, HIST.END_CUSTOMER_ID
820: FROM
821: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS_ALL H, OE_ORDER_HEADER_HISTORY HIST
822: WHERE HIST.SOLD_TO_SITE_USE_ID= M1.DUPLICATE_SITE_ID
823: AND M1.PROCESS_FLAG='N'
824: AND M1.REQUEST_ID = req_id
825: AND M1.SET_NUMBER = set_num
829: HIST.HEADER_ID , HIST.SOLD_TO_ORG_ID, HIST.INVOICE_TO_ORG_ID,
830: HIST.SHIP_TO_ORG_ID, HIST.SOLD_TO_SITE_USE_ID, HIST.DELIVER_TO_ORG_ID,
831: HIST.END_CUSTOMER_SITE_USE_ID, HIST.END_CUSTOMER_ID
832: FROM
833: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS_ALL H, OE_ORDER_HEADER_HISTORY HIST
834: WHERE HIST.DELIVER_TO_ORG_ID= M1.DUPLICATE_SITE_ID
835: AND M1.PROCESS_FLAG='N'
836: AND M1.REQUEST_ID = req_id
837: AND M1.SET_NUMBER = set_num
841: HIST.HEADER_ID , HIST.SOLD_TO_ORG_ID, HIST.INVOICE_TO_ORG_ID,
842: HIST.SHIP_TO_ORG_ID, HIST.SOLD_TO_SITE_USE_ID, HIST.DELIVER_TO_ORG_ID,
843: HIST.END_CUSTOMER_SITE_USE_ID, HIST.END_CUSTOMER_ID
844: FROM
845: RA_CUSTOMER_MERGES M1, OE_ORDER_HEADERS_ALL H, OE_ORDER_HEADER_HISTORY HIST
846: WHERE HIST.END_CUSTOMER_SITE_USE_ID= M1.DUPLICATE_SITE_ID
847: AND M1.PROCESS_FLAG='N'
848: AND M1.REQUEST_ID = req_id
849: AND M1.SET_NUMBER = set_num
1048: select HEADER_ID
1049: from oe_order_header_history
1050: where ship_to_org_id in
1051: (select m.duplicate_site_id
1052: from ra_customer_merges m
1053: where m.process_flag = 'N'
1054: and m.request_id = req_id
1055: and m.set_number = set_num)
1056: for update nowait;
1060: select HEADER_ID
1061: from oe_order_header_history
1062: where invoice_to_org_id in
1063: (select m.duplicate_site_id
1064: from ra_customer_merges m
1065: where m.process_flag = 'N'
1066: and m.request_id = req_id
1067: and m.set_number = set_num)
1068: for update nowait;
1072: select HEADER_ID
1073: from oe_order_header_history
1074: where deliver_to_org_id in
1075: (select m.duplicate_site_id
1076: from ra_customer_merges m
1077: where m.process_flag = 'N'
1078: and m.request_id = req_id
1079: and m.set_number = set_num)
1080: for update nowait;
1084: select HEADER_ID
1085: from oe_order_header_history
1086: where sold_to_org_id in
1087: (select m.duplicate_id
1088: from ra_customer_merges m
1089: where m.process_flag = 'N'
1090: and m.request_id = req_id
1091: and m.set_number = set_num)
1092: for update nowait;
1095: select HEADER_ID
1096: from oe_order_header_history
1097: where sold_to_site_use_id in
1098: (select m.duplicate_id
1099: from ra_customer_merges m
1100: where m.process_flag = 'N'
1101: and m.request_id = req_id
1102: and m.set_number = set_num)
1103: for update nowait;
1106: select HEADER_ID
1107: from oe_order_header_history
1108: where end_customer_site_use_id in
1109: (select m.duplicate_id
1110: from ra_customer_merges m
1111: where m.process_flag = 'N'
1112: and m.request_id = req_id
1113: and m.set_number = set_num)
1114: for update nowait;
1117: select HEADER_ID
1118: from oe_order_header_history
1119: where end_customer_id in
1120: (select m.duplicate_id
1121: from ra_customer_merges m
1122: where m.process_flag = 'N'
1123: and m.request_id = req_id
1124: and m.set_number = set_num)
1125: for update nowait;
1163: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1164:
1165: UPDATE oe_order_header_history a
1166: set ship_to_org_id = (select distinct m.customer_site_id
1167: from ra_customer_merges m
1168: where a.ship_to_org_id = m.duplicate_site_id
1169: and m.request_id = req_id
1170: and m.process_flag = 'N'
1171: and m.set_number = set_num),
1176: program_application_id =arp_standard.profile.program_application_id,
1177: program_id = arp_standard.profile.program_id,
1178: program_update_date = sysdate
1179: where ship_to_org_id in (select m.duplicate_site_id
1180: from ra_customer_merges m
1181: where m.process_flag = 'N'
1182: and m.request_id = req_id
1183: and m.set_number = set_num);
1184: g_count := sql%rowcount;
1190: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1191:
1192: UPDATE oe_order_header_history a
1193: set invoice_to_org_id = (select distinct m.customer_site_id
1194: from ra_customer_merges m
1195: where a.invoice_to_org_id = m.duplicate_site_id
1196: and m.request_id = req_id
1197: and m.process_flag = 'N'
1198: and m.set_number = set_num),
1203: program_application_id =arp_standard.profile.program_application_id,
1204: program_id = arp_standard.profile.program_id,
1205: program_update_date = sysdate
1206: where invoice_to_org_id in (select m.duplicate_site_id
1207: from ra_customer_merges m
1208: where m.process_flag = 'N'
1209: and m.request_id = req_id
1210: and m.set_number = set_num);
1211: g_count := sql%rowcount;
1216: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1217:
1218: UPDATE oe_order_header_history a
1219: set deliver_to_org_id = (select distinct m.customer_site_id
1220: from ra_customer_merges m
1221: where a.deliver_to_org_id = m.duplicate_site_id
1222: and m.request_id = req_id
1223: and m.process_flag = 'N'
1224: and m.set_number = set_num),
1229: program_application_id =arp_standard.profile.program_application_id,
1230: program_id = arp_standard.profile.program_id,
1231: program_update_date = sysdate
1232: where deliver_to_org_id in (select m.duplicate_site_id
1233: from ra_customer_merges m
1234: where m.process_flag = 'N'
1235: and m.request_id = req_id
1236: and m.set_number = set_num);
1237: g_count := sql%rowcount;
1248: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
1249:
1250: UPDATE oe_order_header_history a
1251: set sold_to_org_id = (select distinct m.customer_id
1252: from ra_customer_merges m
1253: where a.sold_to_org_id = m.duplicate_id
1254: and m.process_flag = 'N'
1255: and m.request_id = req_id
1256: and m.set_number = set_num),
1261: program_application_id =arp_standard.profile.program_application_id,
1262: program_id = arp_standard.profile.program_id,
1263: program_update_date = sysdate
1264: where sold_to_org_id in (select m.duplicate_id
1265: from ra_customer_merges m
1266: where m.process_flag = 'N'
1267: and m.request_id = req_id
1268: and m.set_number = set_num);
1269:
1276: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1277:
1278: UPDATE oe_order_header_history a
1279: set sold_to_site_use_id = (select distinct m.customer_id
1280: from ra_customer_merges m
1281: where a.sold_to_site_use_id = m.duplicate_id
1282: and m.process_flag = 'N'
1283: and m.request_id = req_id
1284: and m.set_number = set_num),
1289: program_application_id =arp_standard.profile.program_application_id,
1290: program_id = arp_standard.profile.program_id,
1291: program_update_date = sysdate
1292: where sold_to_site_use_id in (select m.duplicate_id
1293: from ra_customer_merges m
1294: where m.process_flag = 'N'
1295: and m.request_id = req_id
1296: and m.set_number = set_num);
1297:
1304: arp_message.set_token( 'TABLE_NAME', 'OE_ORDER_HEADER_HISTORY', FALSE );
1305:
1306: UPDATE oe_order_header_history a
1307: set end_customer_site_use_id = (select distinct m.customer_id
1308: from ra_customer_merges m
1309: where a.end_customer_site_use_id = m.duplicate_id
1310: and m.process_flag = 'N'
1311: and m.request_id = req_id
1312: and m.set_number = set_num),
1317: program_application_id =arp_standard.profile.program_application_id,
1318: program_id = arp_standard.profile.program_id,
1319: program_update_date = sysdate
1320: where end_customer_site_use_id in (select m.duplicate_id
1321: from ra_customer_merges m
1322: where m.process_flag = 'N'
1323: and m.request_id = req_id
1324: and m.set_number = set_num);
1325:
1334: arp_message.set_token( 'TABLE_NAME', 'oe_order_header_history', FALSE );
1335:
1336: UPDATE oe_order_header_history a
1337: set end_customer_id = (select distinct m.customer_id
1338: from ra_customer_merges m
1339: where a.end_customer_id = m.duplicate_id
1340: and m.process_flag = 'N'
1341: and m.request_id = req_id
1342: and m.set_number = set_num),
1347: program_application_id =arp_standard.profile.program_application_id,
1348: program_id = arp_standard.profile.program_id,
1349: program_update_date = sysdate
1350: where end_customer_id in (select m.duplicate_id
1351: from ra_customer_merges m
1352: where m.process_flag = 'N'
1353: and m.request_id = req_id
1354: and m.set_number = set_num);
1355:
1384: select HEADER_ID
1385: from oe_headers_iface_all
1386: where ship_to_org_id in
1387: (select m.duplicate_site_id
1388: from ra_customer_merges m
1389: where m.process_flag = 'N'
1390: and m.request_id = req_id
1391: and m.set_number = set_num)
1392: for update nowait;
1396: select HEADER_ID
1397: from oe_headers_iface_all
1398: where invoice_to_org_id in
1399: (select m.duplicate_site_id
1400: from ra_customer_merges m
1401: where m.process_flag = 'N'
1402: and m.request_id = req_id
1403: and m.set_number = set_num)
1404: for update nowait;
1408: select HEADER_ID
1409: from oe_headers_iface_all
1410: where deliver_to_org_id in
1411: (select m.duplicate_site_id
1412: from ra_customer_merges m
1413: where m.process_flag = 'N'
1414: and m.request_id = req_id
1415: and m.set_number = set_num)
1416: for update nowait;
1419: select HEADER_ID
1420: from oe_headers_iface_all
1421: where sold_to_org_id in
1422: (select m.duplicate_id
1423: from ra_customer_merges m
1424: where m.process_flag = 'N'
1425: and m.request_id = req_id
1426: and m.set_number = set_num)
1427: for update nowait;
1459: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1460:
1461: UPDATE oe_headers_iface_all a
1462: set ship_to_org_id = (select distinct m.customer_site_id
1463: from ra_customer_merges m
1464: where a.ship_to_org_id = m.duplicate_site_id
1465: and m.request_id = req_id
1466: and m.process_flag = 'N'
1467: and m.set_number = set_num),
1472: program_application_id =arp_standard.profile.program_application_id,
1473: program_id = arp_standard.profile.program_id,
1474: program_update_date = sysdate
1475: where ship_to_org_id in (select m.duplicate_site_id
1476: from ra_customer_merges m
1477: where m.process_flag = 'N'
1478: and m.request_id = req_id
1479: and m.set_number = set_num);
1480: g_count := sql%rowcount;
1485: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1486:
1487: UPDATE oe_headers_iface_all a
1488: set invoice_to_org_id = (select distinct m.customer_site_id
1489: from ra_customer_merges m
1490: where a.invoice_to_org_id = m.duplicate_site_id
1491: and m.request_id = req_id
1492: and m.process_flag = 'N'
1493: and m.set_number = set_num),
1498: program_application_id =arp_standard.profile.program_application_id,
1499: program_id = arp_standard.profile.program_id,
1500: program_update_date = sysdate
1501: where invoice_to_org_id in (select m.duplicate_site_id
1502: from ra_customer_merges m
1503: where m.process_flag = 'N'
1504: and m.request_id = req_id
1505: and m.set_number = set_num);
1506: g_count := sql%rowcount;
1511: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1512:
1513: UPDATE oe_headers_iface_all a
1514: set deliver_to_org_id = (select distinct m.customer_site_id
1515: from ra_customer_merges m
1516: where a.deliver_to_org_id = m.duplicate_site_id
1517: and m.request_id = req_id
1518: and m.process_flag = 'N'
1519: and m.set_number = set_num),
1524: program_application_id =arp_standard.profile.program_application_id,
1525: program_id = arp_standard.profile.program_id,
1526: program_update_date = sysdate
1527: where deliver_to_org_id in (select m.duplicate_site_id
1528: from ra_customer_merges m
1529: where m.process_flag = 'N'
1530: and m.request_id = req_id
1531: and m.set_number = set_num);
1532: g_count := sql%rowcount;
1542: arp_message.set_token( 'TABLE_NAME', 'oe_headers_iface_all', FALSE );
1543:
1544: UPDATE oe_headers_iface_all a
1545: set sold_to_org_id = (select distinct m.customer_id
1546: from ra_customer_merges m
1547: where a.sold_to_org_id = m.duplicate_id
1548: and m.process_flag = 'N'
1549: and m.request_id = req_id
1550: and m.set_number = set_num),
1555: program_application_id =arp_standard.profile.program_application_id,
1556: program_id = arp_standard.profile.program_id,
1557: program_update_date = sysdate
1558: where sold_to_org_id in (select m.duplicate_id
1559: from ra_customer_merges m
1560: where m.process_flag = 'N'
1561: and m.request_id = req_id
1562: and m.set_number = set_num);
1563:
1592: select HEADER_ID
1593: from OE_HEADER_ACKS
1594: where ship_to_org_id in
1595: (select m.duplicate_site_id
1596: from ra_customer_merges m
1597: where m.process_flag = 'N'
1598: and m.request_id = req_id
1599: and m.set_number = set_num)
1600: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1605: select HEADER_ID
1606: from OE_HEADER_ACKS
1607: where invoice_to_org_id in
1608: (select m.duplicate_site_id
1609: from ra_customer_merges m
1610: where m.process_flag = 'N'
1611: and m.request_id = req_id
1612: and m.set_number = set_num)
1613: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1618: select HEADER_ID
1619: from OE_HEADER_ACKS
1620: where deliver_to_org_id in
1621: (select m.duplicate_site_id
1622: from ra_customer_merges m
1623: where m.process_flag = 'N'
1624: and m.request_id = req_id
1625: and m.set_number = set_num)
1626: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1631: select /* MOAC_SQL_CHANGE */ a.HEADER_ID
1632: from OE_HEADER_ACKS a, OE_ORDER_HEADERS_all h
1633: where a.sold_to_org_id in
1634: (select m.duplicate_id
1635: from ra_customer_merges m
1636: where m.process_flag = 'N'
1637: and m.request_id = req_id
1638: and m.set_number = set_num)
1639: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1645: select HEADER_ID
1646: from OE_HEADER_ACKS
1647: where sold_to_site_use_id in
1648: (select m.duplicate_id
1649: from ra_customer_merges m
1650: where m.process_flag = 'N'
1651: and m.request_id = req_id
1652: and m.set_number = set_num)
1653: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1657: select HEADER_ID
1658: from OE_HEADER_ACKS
1659: where end_customer_site_use_id in
1660: (select m.duplicate_id
1661: from ra_customer_merges m
1662: where m.process_flag = 'N'
1663: and m.request_id = req_id
1664: and m.set_number = set_num)
1665: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1670: select a.HEADER_ID
1671: from OE_HEADER_ACKS a,OE_ORDER_HEADERS h
1672: where a.end_customer_id in
1673: (select m.duplicate_id
1674: from ra_customer_merges m
1675: where m.process_flag = 'N'
1676: and m.request_id = req_id
1677: and m.set_number = set_num)
1678: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
1748: hz_utility_pub.LAST_UPDATE_LOGIN,
1749: hz_utility_pub.LAST_UPDATE_DATE,
1750: hz_utility_pub.LAST_UPDATED_BY
1751: from oe_header_acks h,
1752: ra_customer_merges m
1753: where h.ship_to_org_id=m.duplicate_site_id
1754: and m.process_flag = 'N'
1755: and m.request_id = req_id
1756: and m.set_number = set_num
1762: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1763:
1764: UPDATE OE_HEADER_ACKS a
1765: set ship_to_org_id = (select distinct m.customer_site_id
1766: from ra_customer_merges m
1767: where a.ship_to_org_id = m.duplicate_site_id
1768: and m.request_id = req_id
1769: and m.process_flag = 'N'
1770: and m.set_number = set_num),
1775: program_application_id =arp_standard.profile.program_application_id,
1776: program_id = arp_standard.profile.program_id,
1777: program_update_date = sysdate
1778: where ship_to_org_id in (select m.duplicate_site_id
1779: from ra_customer_merges m
1780: where m.process_flag = 'N'
1781: and m.request_id = req_id
1782: and m.set_number = set_num)
1783: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1815: hz_utility_pub.LAST_UPDATE_LOGIN,
1816: hz_utility_pub.LAST_UPDATE_DATE,
1817: hz_utility_pub.LAST_UPDATED_BY
1818: from oe_header_acks h,
1819: ra_customer_merges m
1820: where h.invoice_to_org_id=m.duplicate_site_id
1821: and m.process_flag = 'N'
1822: and m.request_id = req_id
1823: and m.set_number = set_num
1828: arp_message.set_token( 'TABLE_NAME', 'OE_HEADER_ACKS', FALSE );
1829:
1830: UPDATE OE_HEADER_ACKS a
1831: set invoice_to_org_id = (select distinct m.customer_site_id
1832: from ra_customer_merges m
1833: where a.invoice_to_org_id = m.duplicate_site_id
1834: and m.request_id = req_id
1835: and m.process_flag = 'N'
1836: and m.set_number = set_num),
1841: program_application_id =arp_standard.profile.program_application_id,
1842: program_id = arp_standard.profile.program_id,
1843: program_update_date = sysdate
1844: where invoice_to_org_id in (select m.duplicate_site_id
1845: from ra_customer_merges m
1846: where m.process_flag = 'N'
1847: and m.request_id = req_id
1848: and m.set_number = set_num)
1849: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1884: hz_utility_pub.LAST_UPDATE_LOGIN,
1885: hz_utility_pub.LAST_UPDATE_DATE,
1886: hz_utility_pub.LAST_UPDATED_BY
1887: from oe_header_acks h,
1888: ra_customer_merges m
1889: where h.deliver_to_org_id=m.duplicate_site_id
1890: and m.process_flag = 'N'
1891: and m.request_id = req_id
1892: and m.set_number = set_num
1894: end if;
1895:
1896: UPDATE OE_HEADER_ACKS a
1897: set deliver_to_org_id = (select distinct m.customer_site_id
1898: from ra_customer_merges m
1899: where a.deliver_to_org_id = m.duplicate_site_id
1900: and m.request_id = req_id
1901: and m.process_flag = 'N'
1902: and m.set_number = set_num),
1907: program_application_id =arp_standard.profile.program_application_id,
1908: program_id = arp_standard.profile.program_id,
1909: program_update_date = sysdate
1910: where deliver_to_org_id in (select m.duplicate_site_id
1911: from ra_customer_merges m
1912: where m.process_flag = 'N'
1913: and m.request_id = req_id
1914: and m.set_number = set_num)
1915: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
1955: hz_utility_pub.LAST_UPDATE_LOGIN,
1956: hz_utility_pub.LAST_UPDATE_DATE,
1957: hz_utility_pub.LAST_UPDATED_BY
1958: from oe_header_acks h,
1959: ra_customer_merges m
1960: where h.sold_to_org_id=m.duplicate_site_id
1961: and m.process_flag = 'N'
1962: and m.request_id = req_id
1963: and m.set_number = set_num
1965: end if;
1966:
1967: UPDATE OE_HEADER_ACKS a
1968: set sold_to_org_id = (select distinct m.customer_id
1969: from ra_customer_merges m
1970: where a.sold_to_org_id = m.duplicate_id
1971: and m.process_flag = 'N'
1972: and m.request_id = req_id
1973: and m.set_number = set_num),
1978: program_application_id =arp_standard.profile.program_application_id,
1979: program_id = arp_standard.profile.program_id,
1980: program_update_date = sysdate
1981: where sold_to_org_id in (select m.duplicate_id
1982: from ra_customer_merges m
1983: where m.process_flag = 'N'
1984: and m.request_id = req_id
1985: and m.set_number = set_num)
1986: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
2027: hz_utility_pub.LAST_UPDATE_LOGIN,
2028: hz_utility_pub.LAST_UPDATE_DATE,
2029: hz_utility_pub.LAST_UPDATED_BY
2030: from oe_header_acks h,
2031: ra_customer_merges m
2032: where
2033: h.sold_to_site_use_id=m.duplicate_site_id
2034: and
2035: m.process_flag = 'N'
2039: end if;
2040:
2041: UPDATE OE_HEADER_ACKS a
2042: set sold_to_site_use_id = (select distinct m.customer_id
2043: from ra_customer_merges m
2044: where a.sold_to_site_use_id = m.duplicate_id
2045: and m.process_flag = 'N'
2046: and m.request_id = req_id
2047: and m.set_number = set_num),
2052: program_application_id =arp_standard.profile.program_application_id,
2053: program_id = arp_standard.profile.program_id,
2054: program_update_date = sysdate
2055: where sold_to_site_use_id in (select m.duplicate_id
2056: from ra_customer_merges m
2057: where m.process_flag = 'N'
2058: and m.request_id = req_id
2059: and m.set_number = set_num)
2060: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
2100: hz_utility_pub.LAST_UPDATE_LOGIN,
2101: hz_utility_pub.LAST_UPDATE_DATE,
2102: hz_utility_pub.LAST_UPDATED_BY
2103: from oe_header_acks h,
2104: ra_customer_merges m
2105: where
2106: h.end_customer_site_use_id=m.duplicate_site_id
2107: and
2108: m.process_flag = 'N'
2112: end if;
2113:
2114: UPDATE OE_HEADER_ACKS a
2115: set end_customer_site_use_id = (select distinct m.customer_id
2116: from ra_customer_merges m
2117: where a.end_customer_site_use_id = m.duplicate_id
2118: and m.process_flag = 'N'
2119: and m.request_id = req_id
2120: and m.set_number = set_num),
2126: program_id = arp_standard.profile.program_id,
2127: program_update_date = sysdate
2128: where
2129: end_customer_site_use_id in (select m.duplicate_id
2130: from ra_customer_merges m
2131: where m.process_flag = 'N'
2132: and m.request_id = req_id
2133: and m.set_number = set_num)
2134: and
2173: hz_utility_pub.LAST_UPDATE_LOGIN,
2174: hz_utility_pub.LAST_UPDATE_DATE,
2175: hz_utility_pub.LAST_UPDATED_BY
2176: from oe_header_acks h,
2177: ra_customer_merges m
2178: where
2179: -----bug 13058345 h.end_customer_id=m.duplicate_site_id
2180: h.end_customer_id=m.duplicate_id ----added for bug# 13058344
2181: and
2186: end if;
2187:
2188: UPDATE OE_HEADER_ACKS a
2189: set end_customer_id = (select distinct m.customer_id
2190: from ra_customer_merges m
2191: where a.end_customer_id = m.duplicate_id
2192: and m.process_flag = 'N'
2193: and m.request_id = req_id
2194: and m.set_number = set_num),
2199: program_application_id =arp_standard.profile.program_application_id,
2200: program_id = arp_standard.profile.program_id,
2201: program_update_date = sysdate
2202: where end_customer_id in (select m.duplicate_id
2203: from ra_customer_merges m
2204: where m.process_flag = 'N'
2205: and m.request_id = req_id
2206: and m.set_number = set_num)
2207: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
2234: IS
2235:
2236: CURSOR MERGE_SITES IS
2237: select duplicate_id, customer_id, duplicate_site_id, customer_site_id
2238: from ra_customer_merges m
2239: where m.process_flag = 'N'
2240: and m.request_id = req_id
2241: and m.set_number = set_num;
2242:
2256: deliver_to_org_id,
2257: end_customer_site_use_id,
2258: end_customer_id,
2259: m.customer_merge_id
2260: FROM RA_CUSTOMER_MERGES M,
2261: -- change for bug 3196900
2262: -- OE_ORDER_LINES_ALL L
2263: OE_ORDER_LINES L
2264: WHERE
2285: END_CUSTOMER_SITE_USE_ID, END_CUSTOMER_ID,
2286: NVL(M1.CUSTOMER_MERGE_ID,NVL(M2.CUSTOMER_MERGE_ID,NVL(M3.CUSTOMER_MERGE_ID,
2287: NVL(M4.CUSTOMER_MERGE_ID,NVL(M5.CUSTOMER_MERGE_ID,
2288: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
2289: FROM RA_CUSTOMER_MERGES M1,
2290: RA_CUSTOMER_MERGES M2,
2291: RA_CUSTOMER_MERGES M3,
2292: RA_CUSTOMER_MERGES M4,
2293: RA_CUSTOMER_MERGES M5,
2286: NVL(M1.CUSTOMER_MERGE_ID,NVL(M2.CUSTOMER_MERGE_ID,NVL(M3.CUSTOMER_MERGE_ID,
2287: NVL(M4.CUSTOMER_MERGE_ID,NVL(M5.CUSTOMER_MERGE_ID,
2288: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
2289: FROM RA_CUSTOMER_MERGES M1,
2290: RA_CUSTOMER_MERGES M2,
2291: RA_CUSTOMER_MERGES M3,
2292: RA_CUSTOMER_MERGES M4,
2293: RA_CUSTOMER_MERGES M5,
2294: RA_CUSTOMER_MERGES M6,
2287: NVL(M4.CUSTOMER_MERGE_ID,NVL(M5.CUSTOMER_MERGE_ID,
2288: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
2289: FROM RA_CUSTOMER_MERGES M1,
2290: RA_CUSTOMER_MERGES M2,
2291: RA_CUSTOMER_MERGES M3,
2292: RA_CUSTOMER_MERGES M4,
2293: RA_CUSTOMER_MERGES M5,
2294: RA_CUSTOMER_MERGES M6,
2295: RA_CUSTOMER_MERGES M7,
2288: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
2289: FROM RA_CUSTOMER_MERGES M1,
2290: RA_CUSTOMER_MERGES M2,
2291: RA_CUSTOMER_MERGES M3,
2292: RA_CUSTOMER_MERGES M4,
2293: RA_CUSTOMER_MERGES M5,
2294: RA_CUSTOMER_MERGES M6,
2295: RA_CUSTOMER_MERGES M7,
2296: OE_ORDER_LINES L
2289: FROM RA_CUSTOMER_MERGES M1,
2290: RA_CUSTOMER_MERGES M2,
2291: RA_CUSTOMER_MERGES M3,
2292: RA_CUSTOMER_MERGES M4,
2293: RA_CUSTOMER_MERGES M5,
2294: RA_CUSTOMER_MERGES M6,
2295: RA_CUSTOMER_MERGES M7,
2296: OE_ORDER_LINES L
2297: WHERE ( L.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
2290: RA_CUSTOMER_MERGES M2,
2291: RA_CUSTOMER_MERGES M3,
2292: RA_CUSTOMER_MERGES M4,
2293: RA_CUSTOMER_MERGES M5,
2294: RA_CUSTOMER_MERGES M6,
2295: RA_CUSTOMER_MERGES M7,
2296: OE_ORDER_LINES L
2297: WHERE ( L.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
2298: AND L.INVOICE_TO_ORG_ID = M2.DUPLICATE_SITE_ID(+)
2291: RA_CUSTOMER_MERGES M3,
2292: RA_CUSTOMER_MERGES M4,
2293: RA_CUSTOMER_MERGES M5,
2294: RA_CUSTOMER_MERGES M6,
2295: RA_CUSTOMER_MERGES M7,
2296: OE_ORDER_LINES L
2297: WHERE ( L.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
2298: AND L.INVOICE_TO_ORG_ID = M2.DUPLICATE_SITE_ID(+)
2299: AND L.SHIP_TO_ORG_ID = M3.DUPLICATE_SITE_ID(+)
2348: deliver_to_org_id,
2349: end_customer_site_use_id,
2350: end_customer_id,
2351: m.customer_merge_id
2352: FROM RA_CUSTOMER_MERGES M,
2353: -- changed for bug 3196900
2354: -- OE_ORDER_LINES_ALL L
2355: OE_ORDER_LINES L
2356: WHERE
2376: END_CUSTOMER_SITE_USE_ID, END_CUSTOMER_ID,'N',
2377: NVL(M1.CUSTOMER_MERGE_ID,NVL(M2.CUSTOMER_MERGE_ID,NVL(M3.CUSTOMER_MERGE_ID,
2378: NVL(M4.CUSTOMER_MERGE_ID,NVL(M5.CUSTOMER_MERGE_ID,
2379: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
2380: FROM RA_CUSTOMER_MERGES M1,
2381: RA_CUSTOMER_MERGES M2,
2382: RA_CUSTOMER_MERGES M3,
2383: RA_CUSTOMER_MERGES M4,
2384: RA_CUSTOMER_MERGES M5,
2377: NVL(M1.CUSTOMER_MERGE_ID,NVL(M2.CUSTOMER_MERGE_ID,NVL(M3.CUSTOMER_MERGE_ID,
2378: NVL(M4.CUSTOMER_MERGE_ID,NVL(M5.CUSTOMER_MERGE_ID,
2379: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
2380: FROM RA_CUSTOMER_MERGES M1,
2381: RA_CUSTOMER_MERGES M2,
2382: RA_CUSTOMER_MERGES M3,
2383: RA_CUSTOMER_MERGES M4,
2384: RA_CUSTOMER_MERGES M5,
2385: RA_CUSTOMER_MERGES M6,
2378: NVL(M4.CUSTOMER_MERGE_ID,NVL(M5.CUSTOMER_MERGE_ID,
2379: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
2380: FROM RA_CUSTOMER_MERGES M1,
2381: RA_CUSTOMER_MERGES M2,
2382: RA_CUSTOMER_MERGES M3,
2383: RA_CUSTOMER_MERGES M4,
2384: RA_CUSTOMER_MERGES M5,
2385: RA_CUSTOMER_MERGES M6,
2386: RA_CUSTOMER_MERGES M7,
2379: NVL(M6.CUSTOMER_MERGE_ID,M7.CUSTOMER_MERGE_ID))))))
2380: FROM RA_CUSTOMER_MERGES M1,
2381: RA_CUSTOMER_MERGES M2,
2382: RA_CUSTOMER_MERGES M3,
2383: RA_CUSTOMER_MERGES M4,
2384: RA_CUSTOMER_MERGES M5,
2385: RA_CUSTOMER_MERGES M6,
2386: RA_CUSTOMER_MERGES M7,
2387: OE_ORDER_LINES L
2380: FROM RA_CUSTOMER_MERGES M1,
2381: RA_CUSTOMER_MERGES M2,
2382: RA_CUSTOMER_MERGES M3,
2383: RA_CUSTOMER_MERGES M4,
2384: RA_CUSTOMER_MERGES M5,
2385: RA_CUSTOMER_MERGES M6,
2386: RA_CUSTOMER_MERGES M7,
2387: OE_ORDER_LINES L
2388: WHERE ( L.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
2381: RA_CUSTOMER_MERGES M2,
2382: RA_CUSTOMER_MERGES M3,
2383: RA_CUSTOMER_MERGES M4,
2384: RA_CUSTOMER_MERGES M5,
2385: RA_CUSTOMER_MERGES M6,
2386: RA_CUSTOMER_MERGES M7,
2387: OE_ORDER_LINES L
2388: WHERE ( L.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
2389: AND L.INVOICE_TO_ORG_ID = M2.DUPLICATE_SITE_ID(+)
2382: RA_CUSTOMER_MERGES M3,
2383: RA_CUSTOMER_MERGES M4,
2384: RA_CUSTOMER_MERGES M5,
2385: RA_CUSTOMER_MERGES M6,
2386: RA_CUSTOMER_MERGES M7,
2387: OE_ORDER_LINES L
2388: WHERE ( L.SOLD_TO_ORG_ID = M1.DUPLICATE_ID(+)
2389: AND L.INVOICE_TO_ORG_ID = M2.DUPLICATE_SITE_ID(+)
2390: AND L.SHIP_TO_ORG_ID = M3.DUPLICATE_SITE_ID(+)
2437: L.LINE_ID , L.HEADER_ID, L.LAST_UPDATE_DATE, L.LINE_ID,
2438: 'Y', SOLD_TO_ORG_ID, INVOICE_TO_ORG_ID, SHIP_TO_ORG_ID, INTMED_SHIP_TO_ORG_ID,
2439: DELIVER_TO_ORG_ID, END_CUSTOMER_SITE_USE_ID, END_CUSTOMER_ID, 'N',M1.CUSTOMER_MERGE_ID
2440: FROM
2441: RA_CUSTOMER_MERGES M1, OE_ORDER_LINES L
2442: WHERE L.INVOICE_TO_ORG_ID= M1.DUPLICATE_SITE_ID
2443: AND M1.PROCESS_FLAG='N'
2444: AND M1.REQUEST_ID = req_id
2445: AND M1.SET_NUMBER = set_num
2448: L.LINE_ID , L.HEADER_ID, L.LAST_UPDATE_DATE, L.LINE_ID,
2449: 'Y', SOLD_TO_ORG_ID, INVOICE_TO_ORG_ID, SHIP_TO_ORG_ID, INTMED_SHIP_TO_ORG_ID,
2450: DELIVER_TO_ORG_ID, END_CUSTOMER_SITE_USE_ID, END_CUSTOMER_ID, 'N',M1.CUSTOMER_MERGE_ID
2451: FROM
2452: RA_CUSTOMER_MERGES M1, OE_ORDER_LINES L
2453: WHERE L.SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
2454: AND M1.PROCESS_FLAG='N'
2455: AND M1.REQUEST_ID = req_id
2456: AND M1.SET_NUMBER = set_num
2459: L.LINE_ID , L.HEADER_ID, L.LAST_UPDATE_DATE, L.LINE_ID,
2460: 'Y', SOLD_TO_ORG_ID, INVOICE_TO_ORG_ID, SHIP_TO_ORG_ID, INTMED_SHIP_TO_ORG_ID,
2461: DELIVER_TO_ORG_ID, END_CUSTOMER_SITE_USE_ID, END_CUSTOMER_ID,'N', M1.CUSTOMER_MERGE_ID
2462: FROM
2463: RA_CUSTOMER_MERGES M1, OE_ORDER_LINES L
2464: WHERE L.INTMED_SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
2465: AND M1.PROCESS_FLAG='N'
2466: AND M1.REQUEST_ID = req_id
2467: AND M1.SET_NUMBER = set_num
2470: L.LINE_ID , L.HEADER_ID, L.LAST_UPDATE_DATE, L.LINE_ID,
2471: 'Y', SOLD_TO_ORG_ID, INVOICE_TO_ORG_ID, SHIP_TO_ORG_ID, INTMED_SHIP_TO_ORG_ID,
2472: DELIVER_TO_ORG_ID, END_CUSTOMER_SITE_USE_ID, END_CUSTOMER_ID, 'N',M1.CUSTOMER_MERGE_ID
2473: FROM
2474: RA_CUSTOMER_MERGES M1, OE_ORDER_LINES L
2475: WHERE L.DELIVER_TO_ORG_ID= M1.DUPLICATE_SITE_ID
2476: AND M1.PROCESS_FLAG='N'
2477: AND M1.REQUEST_ID = req_id
2478: AND M1.SET_NUMBER = set_num
2481: L.LINE_ID , L.HEADER_ID, L.LAST_UPDATE_DATE, L.LINE_ID,
2482: 'Y', SOLD_TO_ORG_ID, INVOICE_TO_ORG_ID, SHIP_TO_ORG_ID, INTMED_SHIP_TO_ORG_ID,
2483: DELIVER_TO_ORG_ID, END_CUSTOMER_SITE_USE_ID, END_CUSTOMER_ID, 'N',M1.CUSTOMER_MERGE_ID
2484: FROM
2485: RA_CUSTOMER_MERGES M1, OE_ORDER_LINES L
2486: WHERE L.END_CUSTOMER_SITE_USE_ID= M1.DUPLICATE_SITE_ID
2487: AND M1.PROCESS_FLAG='N'
2488: AND M1.REQUEST_ID = req_id
2489: AND M1.SET_NUMBER = set_num
2842: IS
2843:
2844: CURSOR MERGE_SITES IS
2845: select duplicate_id, customer_id, duplicate_site_id, customer_site_id
2846: from ra_customer_merges m
2847: where m.process_flag = 'N'
2848: and m.request_id = req_id
2849: and m.set_number = set_num;
2850: /* MOAC_SQL_CHANGE */
2863: l.deliver_to_org_id,
2864: l.end_customer_site_use_id,
2865: l.end_customer_id
2866: FROM OE_ORDER_LINES_HISTORY L,
2867: RA_CUSTOMER_MERGES M1,
2868: RA_CUSTOMER_MERGES M2,
2869: RA_CUSTOMER_MERGES M3,
2870: RA_CUSTOMER_MERGES M4,
2871: RA_CUSTOMER_MERGES M5,
2864: l.end_customer_site_use_id,
2865: l.end_customer_id
2866: FROM OE_ORDER_LINES_HISTORY L,
2867: RA_CUSTOMER_MERGES M1,
2868: RA_CUSTOMER_MERGES M2,
2869: RA_CUSTOMER_MERGES M3,
2870: RA_CUSTOMER_MERGES M4,
2871: RA_CUSTOMER_MERGES M5,
2872: RA_CUSTOMER_MERGES M6,
2865: l.end_customer_id
2866: FROM OE_ORDER_LINES_HISTORY L,
2867: RA_CUSTOMER_MERGES M1,
2868: RA_CUSTOMER_MERGES M2,
2869: RA_CUSTOMER_MERGES M3,
2870: RA_CUSTOMER_MERGES M4,
2871: RA_CUSTOMER_MERGES M5,
2872: RA_CUSTOMER_MERGES M6,
2873: RA_CUSTOMER_MERGES M7,
2866: FROM OE_ORDER_LINES_HISTORY L,
2867: RA_CUSTOMER_MERGES M1,
2868: RA_CUSTOMER_MERGES M2,
2869: RA_CUSTOMER_MERGES M3,
2870: RA_CUSTOMER_MERGES M4,
2871: RA_CUSTOMER_MERGES M5,
2872: RA_CUSTOMER_MERGES M6,
2873: RA_CUSTOMER_MERGES M7,
2874: -- changed for bug 3196900
2867: RA_CUSTOMER_MERGES M1,
2868: RA_CUSTOMER_MERGES M2,
2869: RA_CUSTOMER_MERGES M3,
2870: RA_CUSTOMER_MERGES M4,
2871: RA_CUSTOMER_MERGES M5,
2872: RA_CUSTOMER_MERGES M6,
2873: RA_CUSTOMER_MERGES M7,
2874: -- changed for bug 3196900
2875: -- OE_ORDER_LINES_HISTORY L
2868: RA_CUSTOMER_MERGES M2,
2869: RA_CUSTOMER_MERGES M3,
2870: RA_CUSTOMER_MERGES M4,
2871: RA_CUSTOMER_MERGES M5,
2872: RA_CUSTOMER_MERGES M6,
2873: RA_CUSTOMER_MERGES M7,
2874: -- changed for bug 3196900
2875: -- OE_ORDER_LINES_HISTORY L
2876: OE_ORDER_LINES_ALL LN
2869: RA_CUSTOMER_MERGES M3,
2870: RA_CUSTOMER_MERGES M4,
2871: RA_CUSTOMER_MERGES M5,
2872: RA_CUSTOMER_MERGES M6,
2873: RA_CUSTOMER_MERGES M7,
2874: -- changed for bug 3196900
2875: -- OE_ORDER_LINES_HISTORY L
2876: OE_ORDER_LINES_ALL LN
2877: -- Changed for MOAC
2932: l.deliver_to_org_id,
2933: l.end_customer_site_use_id,
2934: l.end_customer_id
2935: FROM OE_ORDER_LINES_HISTORY L ,
2936: RA_CUSTOMER_MERGES M1,
2937: RA_CUSTOMER_MERGES M2,
2938: RA_CUSTOMER_MERGES M3,
2939: RA_CUSTOMER_MERGES M4,
2940: RA_CUSTOMER_MERGES M5,
2933: l.end_customer_site_use_id,
2934: l.end_customer_id
2935: FROM OE_ORDER_LINES_HISTORY L ,
2936: RA_CUSTOMER_MERGES M1,
2937: RA_CUSTOMER_MERGES M2,
2938: RA_CUSTOMER_MERGES M3,
2939: RA_CUSTOMER_MERGES M4,
2940: RA_CUSTOMER_MERGES M5,
2941: RA_CUSTOMER_MERGES M6,
2934: l.end_customer_id
2935: FROM OE_ORDER_LINES_HISTORY L ,
2936: RA_CUSTOMER_MERGES M1,
2937: RA_CUSTOMER_MERGES M2,
2938: RA_CUSTOMER_MERGES M3,
2939: RA_CUSTOMER_MERGES M4,
2940: RA_CUSTOMER_MERGES M5,
2941: RA_CUSTOMER_MERGES M6,
2942: RA_CUSTOMER_MERGES M7,
2935: FROM OE_ORDER_LINES_HISTORY L ,
2936: RA_CUSTOMER_MERGES M1,
2937: RA_CUSTOMER_MERGES M2,
2938: RA_CUSTOMER_MERGES M3,
2939: RA_CUSTOMER_MERGES M4,
2940: RA_CUSTOMER_MERGES M5,
2941: RA_CUSTOMER_MERGES M6,
2942: RA_CUSTOMER_MERGES M7,
2943: --changed for bug 3196900
2936: RA_CUSTOMER_MERGES M1,
2937: RA_CUSTOMER_MERGES M2,
2938: RA_CUSTOMER_MERGES M3,
2939: RA_CUSTOMER_MERGES M4,
2940: RA_CUSTOMER_MERGES M5,
2941: RA_CUSTOMER_MERGES M6,
2942: RA_CUSTOMER_MERGES M7,
2943: --changed for bug 3196900
2944: -- OE_ORDER_LINES_HISTORY L
2937: RA_CUSTOMER_MERGES M2,
2938: RA_CUSTOMER_MERGES M3,
2939: RA_CUSTOMER_MERGES M4,
2940: RA_CUSTOMER_MERGES M5,
2941: RA_CUSTOMER_MERGES M6,
2942: RA_CUSTOMER_MERGES M7,
2943: --changed for bug 3196900
2944: -- OE_ORDER_LINES_HISTORY L
2945: OE_ORDER_LINES_ALL LN
2938: RA_CUSTOMER_MERGES M3,
2939: RA_CUSTOMER_MERGES M4,
2940: RA_CUSTOMER_MERGES M5,
2941: RA_CUSTOMER_MERGES M6,
2942: RA_CUSTOMER_MERGES M7,
2943: --changed for bug 3196900
2944: -- OE_ORDER_LINES_HISTORY L
2945: OE_ORDER_LINES_ALL LN
2946: --Changed for MOAC
2999: SELECT L.LINE_ID , L.SOLD_TO_ORG_ID, L.INVOICE_TO_ORG_ID, L.SHIP_TO_ORG_ID,
3000: L.INTMED_SHIP_TO_ORG_ID, L.DELIVER_TO_ORG_ID, L.END_CUSTOMER_SITE_USE_ID,
3001: L.END_CUSTOMER_ID
3002: FROM
3003: RA_CUSTOMER_MERGES M1, OE_ORDER_LINES_HISTORY L, OE_ORDER_LINES_ALL LN
3004: WHERE L.INVOICE_TO_ORG_ID= M1.DUPLICATE_SITE_ID
3005: AND M1.PROCESS_FLAG='N'
3006: AND M1.REQUEST_ID = req_id
3007: AND M1.SET_NUMBER = set_num
3010: SELECT L.LINE_ID , L.SOLD_TO_ORG_ID, L.INVOICE_TO_ORG_ID, L.SHIP_TO_ORG_ID,
3011: L.INTMED_SHIP_TO_ORG_ID, L.DELIVER_TO_ORG_ID, L.END_CUSTOMER_SITE_USE_ID,
3012: L.END_CUSTOMER_ID
3013: FROM
3014: RA_CUSTOMER_MERGES M1, OE_ORDER_LINES_HISTORY L, OE_ORDER_LINES_ALL LN
3015: WHERE L.SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
3016: AND M1.PROCESS_FLAG='N'
3017: AND M1.REQUEST_ID = req_id
3018: AND M1.SET_NUMBER = set_num
3021: SELECT L.LINE_ID , L.SOLD_TO_ORG_ID, L.INVOICE_TO_ORG_ID, L.SHIP_TO_ORG_ID,
3022: L.INTMED_SHIP_TO_ORG_ID, L.DELIVER_TO_ORG_ID, L.END_CUSTOMER_SITE_USE_ID,
3023: L.END_CUSTOMER_ID
3024: FROM
3025: RA_CUSTOMER_MERGES M1, OE_ORDER_LINES_HISTORY L, OE_ORDER_LINES_ALL LN
3026: WHERE L.INTMED_SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
3027: AND M1.PROCESS_FLAG='N'
3028: AND M1.REQUEST_ID = req_id
3029: AND M1.SET_NUMBER = set_num
3032: SELECT L.LINE_ID , L.SOLD_TO_ORG_ID, L.INVOICE_TO_ORG_ID, L.SHIP_TO_ORG_ID,
3033: L.INTMED_SHIP_TO_ORG_ID, L.DELIVER_TO_ORG_ID, L.END_CUSTOMER_SITE_USE_ID,
3034: L.END_CUSTOMER_ID
3035: FROM
3036: RA_CUSTOMER_MERGES M1, OE_ORDER_LINES_HISTORY L, OE_ORDER_LINES_ALL LN
3037: WHERE L.DELIVER_TO_ORG_ID= M1.DUPLICATE_SITE_ID
3038: AND M1.PROCESS_FLAG='N'
3039: AND M1.REQUEST_ID = req_id
3040: AND M1.SET_NUMBER = set_num
3043: SELECT L.LINE_ID , L.SOLD_TO_ORG_ID, L.INVOICE_TO_ORG_ID, L.SHIP_TO_ORG_ID,
3044: L.INTMED_SHIP_TO_ORG_ID, L.DELIVER_TO_ORG_ID, L.END_CUSTOMER_SITE_USE_ID,
3045: L.END_CUSTOMER_ID
3046: FROM
3047: RA_CUSTOMER_MERGES M1, OE_ORDER_LINES_HISTORY L, OE_ORDER_LINES_ALL LN
3048: WHERE L.END_CUSTOMER_SITE_USE_ID= M1.DUPLICATE_SITE_ID
3049: AND M1.PROCESS_FLAG='N'
3050: AND M1.REQUEST_ID = req_id
3051: AND M1.SET_NUMBER = set_num
3259: select line_id
3260: from oe_lines_iface_all
3261: where ship_to_org_id in
3262: (select m.duplicate_site_id
3263: from ra_customer_merges m
3264: where m.process_flag = 'N'
3265: and m.request_id = req_id
3266: and m.set_number = set_num)
3267: for update nowait;
3271: select line_id
3272: from oe_lines_iface_all
3273: where invoice_to_org_id in
3274: (select m.duplicate_site_id
3275: from ra_customer_merges m
3276: where m.process_flag = 'N'
3277: and m.request_id = req_id
3278: and m.set_number = set_num)
3279: for update nowait;
3283: select line_id
3284: from oe_lines_iface_all
3285: where deliver_to_org_id in
3286: (select m.duplicate_site_id
3287: from ra_customer_merges m
3288: where m.process_flag = 'N'
3289: and m.request_id = req_id
3290: and m.set_number = set_num)
3291: for update nowait;
3295: select line_id
3296: from oe_lines_iface_all
3297: where sold_to_org_id in
3298: (select m.duplicate_id
3299: from ra_customer_merges m
3300: where m.process_flag = 'N'
3301: and m.request_id = req_id
3302: and m.set_number = set_num)
3303: for update nowait;
3330: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3331:
3332: UPDATE oe_lines_iface_all a
3333: set ship_to_org_id = (select distinct m.customer_site_id
3334: from ra_customer_merges m
3335: where a.ship_to_org_id = m.duplicate_site_id
3336: and m.request_id = req_id
3337: and m.process_flag = 'N'
3338: and m.set_number = set_num),
3343: program_application_id =arp_standard.profile.program_application_id,
3344: program_id = arp_standard.profile.program_id,
3345: program_update_date = sysdate
3346: where ship_to_org_id in (select m.duplicate_site_id
3347: from ra_customer_merges m
3348: where m.process_flag = 'N'
3349: and m.request_id = req_id
3350: and m.set_number = set_num);
3351: g_count := sql%rowcount;
3356: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3357:
3358: UPDATE oe_lines_iface_all a
3359: set invoice_to_org_id = (select distinct m.customer_site_id
3360: from ra_customer_merges m
3361: where a.invoice_to_org_id = m.duplicate_site_id
3362: and m.request_id = req_id
3363: and m.process_flag = 'N'
3364: and m.set_number = set_num),
3369: program_application_id =arp_standard.profile.program_application_id,
3370: program_id = arp_standard.profile.program_id,
3371: program_update_date = sysdate
3372: where invoice_to_org_id in (select m.duplicate_site_id
3373: from ra_customer_merges m
3374: where m.process_flag = 'N'
3375: and m.request_id = req_id
3376: and m.set_number = set_num);
3377: g_count := sql%rowcount;
3382: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3383:
3384: UPDATE oe_lines_iface_all a
3385: set deliver_to_org_id = (select distinct m.customer_site_id
3386: from ra_customer_merges m
3387: where a.deliver_to_org_id = m.duplicate_site_id
3388: and m.request_id = req_id
3389: and m.process_flag = 'N'
3390: and m.set_number = set_num),
3395: program_application_id =arp_standard.profile.program_application_id,
3396: program_id = arp_standard.profile.program_id,
3397: program_update_date = sysdate
3398: where deliver_to_org_id in (select m.duplicate_site_id
3399: from ra_customer_merges m
3400: where m.process_flag = 'N'
3401: and m.request_id = req_id
3402: and m.set_number = set_num);
3403: g_count := sql%rowcount;
3412: arp_message.set_token( 'TABLE_NAME', 'oe_lines_iface_all', FALSE );
3413:
3414: UPDATE oe_lines_iface_all a
3415: set sold_to_org_id = (select distinct m.customer_id
3416: from ra_customer_merges m
3417: where a.sold_to_org_id = m.duplicate_id
3418: and m.process_flag = 'N'
3419: and m.request_id = req_id
3420: and m.set_number = set_num),
3425: program_application_id =arp_standard.profile.program_application_id,
3426: program_id = arp_standard.profile.program_id,
3427: program_update_date = sysdate
3428: where sold_to_org_id in (select m.duplicate_id
3429: from ra_customer_merges m
3430: where m.process_flag = 'N'
3431: and m.request_id = req_id
3432: and m.set_number = set_num);
3433:
3460: select line_id
3461: from oe_line_acks
3462: where ship_to_org_id in
3463: (select m.duplicate_site_id
3464: from ra_customer_merges m
3465: where m.process_flag = 'N'
3466: and m.request_id = req_id
3467: and m.set_number = set_num)
3468: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
3473: select line_id
3474: from oe_line_acks
3475: where invoice_to_org_id in
3476: (select m.duplicate_site_id
3477: from ra_customer_merges m
3478: where m.process_flag = 'N'
3479: and m.request_id = req_id
3480: and m.set_number = set_num)
3481: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
3486: select line_id
3487: from oe_line_acks
3488: where deliver_to_org_id in
3489: (select m.duplicate_site_id
3490: from ra_customer_merges m
3491: where m.process_flag = 'N'
3492: and m.request_id = req_id
3493: and m.set_number = set_num)
3494: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
3499: select line_id
3500: from oe_line_acks
3501: where intmed_ship_to_org_id in
3502: (select m.duplicate_site_id
3503: from ra_customer_merges m
3504: where m.process_flag = 'N'
3505: and m.request_id = req_id
3506: and m.set_number = set_num)
3507: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
3512: select a.line_id
3513: from oe_line_acks a, oe_order_lines l
3514: where a.sold_to_org_id in
3515: (select m.duplicate_id
3516: from ra_customer_merges m
3517: where m.process_flag = 'N'
3518: and m.request_id = req_id
3519: and m.set_number = set_num)
3520: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
3526: select line_id
3527: from oe_line_acks
3528: where end_customer_site_use_id in
3529: (select m.duplicate_id
3530: from ra_customer_merges m
3531: where m.process_flag = 'N'
3532: and m.request_id = req_id
3533: and m.set_number = set_num)
3534: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
3539: select a.line_id
3540: from oe_line_acks a,oe_order_lines l
3541: where a.end_customer_id in
3542: (select m.duplicate_id
3543: from ra_customer_merges m
3544: where m.process_flag = 'N'
3545: and m.request_id = req_id
3546: and m.set_number = set_num)
3547: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
3619: hz_utility_pub.LAST_UPDATE_LOGIN,
3620: hz_utility_pub.LAST_UPDATE_DATE,
3621: hz_utility_pub.LAST_UPDATED_BY
3622: from oe_line_acks a,
3623: ra_customer_merges m
3624: where m.process_flag = 'N'
3625: and a.ship_to_org_id = m.duplicate_site_id
3626: and m.request_id = req_id
3627: and m.set_number = set_num
3633: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3634:
3635: UPDATE oe_line_acks a
3636: set ship_to_org_id = (select distinct m.customer_site_id
3637: from ra_customer_merges m
3638: where a.ship_to_org_id = m.duplicate_site_id
3639: and m.request_id = req_id
3640: and m.process_flag = 'N'
3641: and m.set_number = set_num),
3646: program_application_id =arp_standard.profile.program_application_id,
3647: program_id = arp_standard.profile.program_id,
3648: program_update_date = sysdate
3649: where ship_to_org_id in (select m.duplicate_site_id
3650: from ra_customer_merges m
3651: where m.process_flag = 'N'
3652: and m.request_id = req_id
3653: and m.set_number = set_num)
3654: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3688: hz_utility_pub.LAST_UPDATE_LOGIN,
3689: hz_utility_pub.LAST_UPDATE_DATE,
3690: hz_utility_pub.LAST_UPDATED_BY
3691: from oe_line_acks a,
3692: ra_customer_merges m
3693: where m.process_flag = 'N'
3694: and a.invoice_to_org_id = m.duplicate_site_id
3695: and m.request_id = req_id
3696: and m.set_number = set_num
3701: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3702:
3703: UPDATE oe_line_acks a
3704: set invoice_to_org_id = (select distinct m.customer_site_id
3705: from ra_customer_merges m
3706: where a.invoice_to_org_id = m.duplicate_site_id
3707: and m.request_id = req_id
3708: and m.process_flag = 'N'
3709: and m.set_number = set_num),
3714: program_application_id =arp_standard.profile.program_application_id,
3715: program_id = arp_standard.profile.program_id,
3716: program_update_date = sysdate
3717: where invoice_to_org_id in (select m.duplicate_site_id
3718: from ra_customer_merges m
3719: where m.process_flag = 'N'
3720: and m.request_id = req_id
3721: and m.set_number = set_num)
3722: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3756: hz_utility_pub.LAST_UPDATE_LOGIN,
3757: hz_utility_pub.LAST_UPDATE_DATE,
3758: hz_utility_pub.LAST_UPDATED_BY
3759: from oe_line_acks a,
3760: ra_customer_merges m
3761: where m.process_flag = 'N'
3762: and a.deliver_to_org_id = m.duplicate_site_id
3763: and m.request_id = req_id
3764: and m.set_number = set_num
3769: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3770:
3771: UPDATE oe_line_acks a
3772: set deliver_to_org_id = (select distinct m.customer_site_id
3773: from ra_customer_merges m
3774: where a.deliver_to_org_id = m.duplicate_site_id
3775: and m.request_id = req_id
3776: and m.process_flag = 'N'
3777: and m.set_number = set_num),
3782: program_application_id =arp_standard.profile.program_application_id,
3783: program_id = arp_standard.profile.program_id,
3784: program_update_date = sysdate
3785: where deliver_to_org_id in (select m.duplicate_site_id
3786: from ra_customer_merges m
3787: where m.process_flag = 'N'
3788: and m.request_id = req_id
3789: and m.set_number = set_num)
3790: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3824: hz_utility_pub.LAST_UPDATE_LOGIN,
3825: hz_utility_pub.LAST_UPDATE_DATE,
3826: hz_utility_pub.LAST_UPDATED_BY
3827: from oe_line_acks a,
3828: ra_customer_merges m
3829: where m.process_flag = 'N'
3830: and a.intmed_ship_to_org_id = m.duplicate_site_id
3831: and m.request_id = req_id
3832: and m.set_number = set_num
3838:
3839:
3840: UPDATE oe_line_acks a
3841: set intmed_ship_to_org_id = (select distinct m.customer_site_id
3842: from ra_customer_merges m
3843: where a.intmed_ship_to_org_id = m.duplicate_site_id
3844: and m.request_id = req_id
3845: and m.process_flag = 'N'
3846: and m.set_number = set_num),
3851: program_application_id =arp_standard.profile.program_application_id,
3852: program_id = arp_standard.profile.program_id,
3853: program_update_date = sysdate
3854: where intmed_ship_to_org_id in (select m.duplicate_site_id
3855: from ra_customer_merges m
3856: where m.process_flag = 'N'
3857: and m.request_id = req_id
3858: and m.set_number = set_num)
3859: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
3898: hz_utility_pub.LAST_UPDATE_LOGIN,
3899: hz_utility_pub.LAST_UPDATE_DATE,
3900: hz_utility_pub.LAST_UPDATED_BY
3901: from oe_line_acks a,
3902: ra_customer_merges m
3903: where m.process_flag = 'N'
3904: and a.sold_to_org_id = m.duplicate_site_id
3905: and m.request_id = req_id
3906: and m.set_number = set_num
3912: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3913:
3914: UPDATE oe_line_acks a
3915: set sold_to_org_id = (select distinct m.customer_id
3916: from ra_customer_merges m
3917: where a.sold_to_org_id = m.duplicate_id
3918: and m.process_flag = 'N'
3919: and m.request_id = req_id
3920: and m.set_number = set_num),
3925: program_application_id =arp_standard.profile.program_application_id,
3926: program_id = arp_standard.profile.program_id,
3927: program_update_date = sysdate
3928: where sold_to_org_id in (select m.duplicate_id
3929: from ra_customer_merges m
3930: where m.process_flag = 'N'
3931: and m.request_id = req_id
3932: and m.set_number = set_num)
3933: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
3975: hz_utility_pub.LAST_UPDATE_LOGIN,
3976: hz_utility_pub.LAST_UPDATE_DATE,
3977: hz_utility_pub.LAST_UPDATED_BY
3978: from oe_line_acks a,
3979: ra_customer_merges m
3980: where m.process_flag = 'N'
3981: and a.sold_to_org_id = m.duplicate_site_id
3982: and m.request_id = req_id
3983: and m.set_number = set_num
3989: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
3990:
3991: UPDATE oe_line_acks a
3992: set end_customer_site_use_id = (select distinct m.customer_id
3993: from ra_customer_merges m
3994: where a.end_customer_site_use_id = m.duplicate_id
3995: and m.process_flag = 'N'
3996: and m.request_id = req_id
3997: and m.set_number = set_num),
4002: program_application_id =arp_standard.profile.program_application_id,
4003: program_id = arp_standard.profile.program_id,
4004: program_update_date = sysdate
4005: where end_customer_site_use_id in (select m.duplicate_id
4006: from ra_customer_merges m
4007: where m.process_flag = 'N'
4008: and m.request_id = req_id
4009: and m.set_number = set_num)
4010: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y';
4050: hz_utility_pub.LAST_UPDATE_LOGIN,
4051: hz_utility_pub.LAST_UPDATE_DATE,
4052: hz_utility_pub.LAST_UPDATED_BY
4053: from oe_line_acks a,
4054: ra_customer_merges m
4055: where m.process_flag = 'N'
4056: and a.end_customer_id = m.duplicate_id ---replaced duplicate_site_id by duplicate_id bug 13058344
4057: and m.request_id = req_id
4058: and m.set_number = set_num
4064: arp_message.set_token( 'TABLE_NAME', 'oe_line_acks', FALSE );
4065:
4066: UPDATE oe_line_acks a
4067: set end_customer_id = (select distinct m.customer_id
4068: from ra_customer_merges m
4069: where a.end_customer_id = m.duplicate_id
4070: and m.process_flag = 'N'
4071: and m.request_id = req_id
4072: and m.set_number = set_num),
4077: program_application_id =arp_standard.profile.program_application_id,
4078: program_id = arp_standard.profile.program_id,
4079: program_update_date = sysdate
4080: where end_customer_id in (select m.duplicate_id
4081: from ra_customer_merges m
4082: where m.process_flag = 'N'
4083: and m.request_id = req_id
4084: and m.set_number = set_num)
4085: and NVL(ACKNOWLEDGMENT_FLAG,'N') <> 'Y'
4162: CURSOR c1 is
4163: select RULE_ELEMENT_ID
4164: from oe_attachment_rule_elements
4165: where attribute_value in (select to_char(m.duplicate_site_id)
4166: from ra_customer_merges m
4167: where m.process_flag = 'N'
4168: and m.request_id = req_id
4169: and m.set_number = set_num)
4170: and attribute_code = 'SHIP_TO_ORG_ID'
4173: CURSOR c2 is
4174: select RULE_ELEMENT_ID
4175: from oe_attachment_rule_elements
4176: where attribute_value in (select to_char(m.duplicate_site_id)
4177: from ra_customer_merges m
4178: where m.process_flag = 'N'
4179: and m.request_id = req_id
4180: and m.set_number = set_num)
4181: and attribute_code = 'INVOICE_TO_ORG_ID'
4184: CURSOR c3 is
4185: select RULE_ELEMENT_ID
4186: from oe_attachment_rule_elements
4187: where attribute_value in (select to_char(m.duplicate_id)
4188: from ra_customer_merges m
4189: where m.process_flag = 'N'
4190: and m.request_id = req_id
4191: and m.set_number = set_num)
4192: and attribute_code = 'SOLD_TO_ORG_ID'
4252: hz_utility_pub.LAST_UPDATE_LOGIN,
4253: hz_utility_pub.LAST_UPDATE_DATE,
4254: hz_utility_pub.LAST_UPDATED_BY
4255: from OE_ATTACHMENT_RULE_ELEMENTS a,
4256: ra_customer_merges m
4257: where a.attribute_value = to_char(m.duplicate_site_id)
4258: and m.process_flag = 'N'
4259: and m.request_id = req_id
4260: and m.set_number = set_num
4267: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
4268:
4269: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
4270: set (attribute_value) = (select distinct to_char(m.customer_site_id)
4271: from ra_customer_merges m
4272: where a.attribute_value =
4273: to_char(m.duplicate_site_id)
4274: and m.request_id = req_id
4275: and m.process_flag = 'N'
4277: last_update_date = sysdate,
4278: last_updated_by = arp_standard.profile.user_id,
4279: last_update_login = arp_standard.profile.last_update_login
4280: where attribute_value in (select to_char(m.duplicate_site_id)
4281: from ra_customer_merges m
4282: where m.process_flag = 'N'
4283: and m.request_id = req_id
4284: and m.set_number = set_num)
4285: and attribute_code = 'SHIP_TO_ORG_ID';
4320: hz_utility_pub.LAST_UPDATE_LOGIN,
4321: hz_utility_pub.LAST_UPDATE_DATE,
4322: hz_utility_pub.LAST_UPDATED_BY
4323: from OE_ATTACHMENT_RULE_ELEMENTS a,
4324: ra_customer_merges m
4325: where a.attribute_value = to_char(m.duplicate_site_id)
4326: and m.process_flag = 'N'
4327: and m.request_id = req_id
4328: and m.set_number = set_num
4335: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
4336:
4337: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
4338: set (attribute_value) = (select distinct to_char(m.customer_site_id)
4339: from ra_customer_merges m
4340: where a.attribute_value =
4341: to_char(m.duplicate_site_id)
4342: and m.request_id = req_id
4343: and m.process_flag = 'N'
4345: last_update_date = sysdate,
4346: last_updated_by = arp_standard.profile.user_id,
4347: last_update_login = arp_standard.profile.last_update_login
4348: where attribute_value in (select to_char(m.duplicate_site_id)
4349: from ra_customer_merges m
4350: where m.process_flag = 'N'
4351: and m.request_id = req_id
4352: and m.set_number = set_num)
4353: and attribute_code = 'INVOICE_TO_ORG_ID';
4389: hz_utility_pub.LAST_UPDATE_LOGIN,
4390: hz_utility_pub.LAST_UPDATE_DATE,
4391: hz_utility_pub.LAST_UPDATED_BY
4392: from OE_ATTACHMENT_RULE_ELEMENTS a,
4393: ra_customer_merges m
4394: where a.attribute_value = to_char(m.duplicate_site_id)
4395: and m.process_flag = 'N'
4396: and m.request_id = req_id
4397: and m.set_number = set_num
4404: arp_message.set_token( 'TABLE_NAME', 'OE_ATTACHMENT_RULE_ELEMENTS', FALSE );
4405:
4406: UPDATE OE_ATTACHMENT_RULE_ELEMENTS a
4407: set (attribute_value) = (select distinct to_char(m.customer_id)
4408: from ra_customer_merges m
4409: where a.attribute_value =
4410: to_char(m.duplicate_id)
4411: and m.request_id = req_id
4412: and m.process_flag = 'N'
4414: last_update_date = sysdate,
4415: last_updated_by = arp_standard.profile.user_id,
4416: last_update_login = arp_standard.profile.last_update_login
4417: where attribute_value in (select to_char(m.duplicate_id)
4418: from ra_customer_merges m
4419: where m.process_flag = 'N'
4420: and m.request_id = req_id
4421: and m.set_number = set_num)
4422: and attribute_code = 'SOLD_TO_ORG_ID';
4449: CURSOR c1 is
4450: select CONDITION_ELEMENT_ID
4451: from oe_def_condn_elems
4452: where value_string in (select to_char(m.duplicate_site_id)
4453: from ra_customer_merges m
4454: where m.process_flag = 'N'
4455: and m.request_id = req_id
4456: and m.set_number = set_num)
4457: and attribute_code = 'SHIP_TO_ORG_ID'
4460: CURSOR c2 is
4461: select CONDITION_ELEMENT_ID
4462: from oe_def_condn_elems
4463: where value_string in (select to_char(m.duplicate_site_id)
4464: from ra_customer_merges m
4465: where m.process_flag = 'N'
4466: and m.request_id = req_id
4467: and m.set_number = set_num)
4468: and attribute_code = 'INVOICE_TO_ORG_ID'
4471: CURSOR c4 is
4472: select CONDITION_ELEMENT_ID
4473: from oe_def_condn_elems
4474: where value_string in (select to_char(m.duplicate_site_id)
4475: from ra_customer_merges m
4476: where m.process_flag = 'N'
4477: and m.request_id = req_id
4478: and m.set_number = set_num)
4479: and attribute_code = 'INTMED_SHIP_TO_ORG_ID'
4482: CURSOR c3 is
4483: select CONDITION_ELEMENT_ID
4484: from oe_def_condn_elems
4485: where value_string in (select to_char(m.duplicate_id)
4486: from ra_customer_merges m
4487: where m.process_flag = 'N'
4488: and m.request_id = req_id
4489: and m.set_number = set_num)
4490: and attribute_code = 'SOLD_TO_ORG_ID'
4493: CURSOR c5 is
4494: select ATTR_DEF_RULE_ID
4495: from oe_def_attr_def_rules
4496: where src_constant_value in (select to_char(m.duplicate_site_id)
4497: from ra_customer_merges m
4498: where m.process_flag = 'N'
4499: and m.request_id = req_id
4500: and m.set_number = set_num)
4501: and attribute_code = 'SHIP_TO_ORG_ID'
4504: CURSOR c6 is
4505: select ATTR_DEF_RULE_ID
4506: from oe_def_attr_def_rules
4507: where src_constant_value in (select to_char(m.duplicate_site_id)
4508: from ra_customer_merges m
4509: where m.process_flag = 'N'
4510: and m.request_id = req_id
4511: and m.set_number = set_num)
4512: and attribute_code = 'INVOICE_TO_ORG_ID'
4515: CURSOR c7 is
4516: select ATTR_DEF_RULE_ID
4517: from oe_def_attr_def_rules
4518: where src_constant_value in (select to_char(m.duplicate_site_id)
4519: from ra_customer_merges m
4520: where m.process_flag = 'N'
4521: and m.request_id = req_id
4522: and m.set_number = set_num)
4523: and attribute_code = 'INTMED_SHIP_TO_ORG_ID'
4526: CURSOR c8 is
4527: select ATTR_DEF_RULE_ID
4528: from oe_def_attr_def_rules
4529: where src_constant_value in (select to_char(m.duplicate_id)
4530: from ra_customer_merges m
4531: where m.process_flag = 'N'
4532: and m.request_id = req_id
4533: and m.set_number = set_num)
4534: and attribute_code = 'SOLD_TO_ORG_ID'
4609: hz_utility_pub.LAST_UPDATE_LOGIN,
4610: hz_utility_pub.LAST_UPDATE_DATE,
4611: hz_utility_pub.LAST_UPDATED_BY
4612: from OE_DEF_CONDN_ELEMS a,
4613: ra_customer_merges m
4614: where a.value_string = to_char(m.duplicate_site_id)
4615: and m.process_flag = 'N'
4616: and m.request_id = req_id
4617: and m.set_number = set_num
4624: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4625:
4626: UPDATE OE_DEF_CONDN_ELEMS a
4627: set value_string = (select distinct to_char(m.customer_site_id)
4628: from ra_customer_merges m
4629: where a.value_string =
4630: to_char(m.duplicate_site_id)
4631:
4632: and m.request_id = req_id
4635: last_update_date = sysdate,
4636: last_updated_by = arp_standard.profile.user_id,
4637: last_update_login = arp_standard.profile.last_update_login
4638: where value_string in (select to_char(m.duplicate_site_id)
4639: from ra_customer_merges m
4640: where m.process_flag = 'N'
4641: and m.request_id = req_id
4642: and m.set_number = set_num)
4643: and attribute_code = 'SHIP_TO_ORG_ID';
4676: hz_utility_pub.LAST_UPDATE_LOGIN,
4677: hz_utility_pub.LAST_UPDATE_DATE,
4678: hz_utility_pub.LAST_UPDATED_BY
4679: from OE_DEF_CONDN_ELEMS a,
4680: ra_customer_merges m
4681: where a.value_string = to_char(m.duplicate_site_id)
4682: and m.process_flag = 'N'
4683: and m.request_id = req_id
4684: and m.set_number = set_num
4692: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4693:
4694: UPDATE OE_DEF_CONDN_ELEMS a
4695: set value_string = (select distinct to_char(m.customer_site_id)
4696: from ra_customer_merges m
4697: where a.value_string =
4698: to_char(m.duplicate_site_id)
4699:
4700: and m.request_id = req_id
4703: last_update_date = sysdate,
4704: last_updated_by = arp_standard.profile.user_id,
4705: last_update_login = arp_standard.profile.last_update_login
4706: where value_string in (select to_char(m.duplicate_site_id)
4707: from ra_customer_merges m
4708: where m.process_flag = 'N'
4709: and m.request_id = req_id
4710: and m.set_number = set_num)
4711: and attribute_code = 'INVOICE_TO_ORG_ID';
4745: hz_utility_pub.LAST_UPDATE_LOGIN,
4746: hz_utility_pub.LAST_UPDATE_DATE,
4747: hz_utility_pub.LAST_UPDATED_BY
4748: from OE_DEF_CONDN_ELEMS a,
4749: ra_customer_merges m
4750: where a.value_string = to_char(m.duplicate_site_id)
4751: and m.process_flag = 'N'
4752: and m.request_id = req_id
4753: and m.set_number = set_num
4760: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4761:
4762: UPDATE OE_DEF_CONDN_ELEMS a
4763: set value_string = (select distinct to_char(m.customer_site_id)
4764: from ra_customer_merges m
4765: where a.value_string =
4766: to_char(m.duplicate_site_id)
4767:
4768: and m.request_id = req_id
4771: last_update_date = sysdate,
4772: last_updated_by = arp_standard.profile.user_id,
4773: last_update_login = arp_standard.profile.last_update_login
4774: where value_string in (select to_char(m.duplicate_site_id)
4775: from ra_customer_merges m
4776: where m.process_flag = 'N'
4777: and m.request_id = req_id
4778: and m.set_number = set_num)
4779: and attribute_code = 'INTMED_SHIP_TO_ORG_ID';
4813: hz_utility_pub.LAST_UPDATE_LOGIN,
4814: hz_utility_pub.LAST_UPDATE_DATE,
4815: hz_utility_pub.LAST_UPDATED_BY
4816: from OE_DEF_CONDN_ELEMS a,
4817: ra_customer_merges m
4818: where a.value_string = to_char(m.duplicate_id)
4819: and m.process_flag = 'N'
4820: and m.request_id = req_id
4821: and m.set_number = set_num
4828: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_CONDN_ELEMS', FALSE );
4829:
4830: UPDATE OE_DEF_CONDN_ELEMS a
4831: set value_string = (select distinct to_char(m.customer_id)
4832: from ra_customer_merges m
4833: where a.value_string =
4834: to_char(m.duplicate_id)
4835:
4836: and m.request_id = req_id
4839: last_update_date = sysdate,
4840: last_updated_by = arp_standard.profile.user_id,
4841: last_update_login = arp_standard.profile.last_update_login
4842: where value_string in (select to_char(m.duplicate_id)
4843: from ra_customer_merges m
4844: where m.process_flag = 'N'
4845: and m.request_id = req_id
4846: and m.set_number = set_num)
4847: and attribute_code = 'SOLD_TO_ORG_ID';
4883: hz_utility_pub.LAST_UPDATE_LOGIN,
4884: hz_utility_pub.LAST_UPDATE_DATE,
4885: hz_utility_pub.LAST_UPDATED_BY
4886: from OE_DEF_ATTR_DEF_RULES a,
4887: ra_customer_merges m
4888: where a.src_constant_value = to_char(m.duplicate_site_id)
4889: and m.process_flag = 'N'
4890: and m.request_id = req_id
4891: and m.set_number = set_num
4900:
4901:
4902: UPDATE OE_DEF_ATTR_DEF_RULES a
4903: set src_constant_value = (select distinct to_char(m.customer_site_id)
4904: from ra_customer_merges m
4905: where a.src_constant_value =
4906: to_char(m.duplicate_site_id)
4907:
4908: and m.request_id = req_id
4911: last_update_date = sysdate,
4912: last_updated_by = arp_standard.profile.user_id,
4913: last_update_login = arp_standard.profile.last_update_login
4914: where src_constant_value in (select to_char(m.duplicate_site_id)
4915: from ra_customer_merges m
4916: where m.process_flag = 'N'
4917: and m.request_id = req_id
4918: and m.set_number = set_num)
4919: and attribute_code = 'SHIP_TO_ORG_ID';
4953: hz_utility_pub.LAST_UPDATE_LOGIN,
4954: hz_utility_pub.LAST_UPDATE_DATE,
4955: hz_utility_pub.LAST_UPDATED_BY
4956: from OE_DEF_ATTR_DEF_RULES a,
4957: ra_customer_merges m
4958: where a.src_constant_value = to_char(m.duplicate_site_id)
4959: and m.process_flag = 'N'
4960: and m.request_id = req_id
4961: and m.set_number = set_num
4968: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
4969:
4970: UPDATE OE_DEF_ATTR_DEF_RULES a
4971: set src_constant_value = (select distinct to_char(m.customer_site_id)
4972: from ra_customer_merges m
4973: where a.src_constant_value =
4974: to_char(m.duplicate_site_id)
4975:
4976: and m.request_id = req_id
4979: last_update_date = sysdate,
4980: last_updated_by = arp_standard.profile.user_id,
4981: last_update_login = arp_standard.profile.last_update_login
4982: where src_constant_value in (select to_char(m.duplicate_site_id)
4983: from ra_customer_merges m
4984: where m.process_flag = 'N'
4985: and m.request_id = req_id
4986: and m.set_number = set_num)
4987: and attribute_code = 'INVOICE_TO_ORG_ID';
5021: hz_utility_pub.LAST_UPDATE_LOGIN,
5022: hz_utility_pub.LAST_UPDATE_DATE,
5023: hz_utility_pub.LAST_UPDATED_BY
5024: from OE_DEF_ATTR_DEF_RULES a,
5025: ra_customer_merges m
5026: where a.src_constant_value = to_char(m.duplicate_site_id)
5027: and m.process_flag = 'N'
5028: and m.request_id = req_id
5029: and m.set_number = set_num
5036: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
5037:
5038: UPDATE OE_DEF_ATTR_DEF_RULES a
5039: set src_constant_value = (select distinct to_char(m.customer_site_id)
5040: from ra_customer_merges m
5041: where a.src_constant_value =
5042: to_char(m.duplicate_site_id)
5043:
5044: and m.request_id = req_id
5047: last_update_date = sysdate,
5048: last_updated_by = arp_standard.profile.user_id,
5049: last_update_login = arp_standard.profile.last_update_login
5050: where src_constant_value in (select to_char(m.duplicate_site_id)
5051: from ra_customer_merges m
5052: where m.process_flag = 'N'
5053: and m.request_id = req_id
5054: and m.set_number = set_num)
5055: and attribute_code = 'INTMED_SHIP_TO_ORG_ID';
5090: hz_utility_pub.LAST_UPDATE_LOGIN,
5091: hz_utility_pub.LAST_UPDATE_DATE,
5092: hz_utility_pub.LAST_UPDATED_BY
5093: from OE_DEF_ATTR_DEF_RULES a,
5094: ra_customer_merges m
5095: where a.src_constant_value = to_char(m.duplicate_site_id)
5096: and m.process_flag = 'N'
5097: and m.request_id = req_id
5098: and m.set_number = set_num
5105: arp_message.set_token( 'TABLE_NAME', 'OE_DEF_ATTR_DEF_RULES', FALSE );
5106:
5107: UPDATE OE_DEF_ATTR_DEF_RULES a
5108: set src_constant_value = (select distinct to_char(m.customer_id)
5109: from ra_customer_merges m
5110: where a.src_constant_value =
5111: to_char(m.duplicate_id)
5112:
5113: and m.request_id = req_id
5116: last_update_date = sysdate,
5117: last_updated_by = arp_standard.profile.user_id,
5118: last_update_login = arp_standard.profile.last_update_login
5119: where src_constant_value in (select to_char(m.duplicate_id)
5120: from ra_customer_merges m
5121: where m.process_flag = 'N'
5122: and m.request_id = req_id
5123: and m.set_number = set_num)
5124: and attribute_code = 'SOLD_TO_ORG_ID';
5153: CURSOR c1 is
5154: select VALIDATION_TMPLT_ID
5155: from oe_pc_vtmplt_cols
5156: where value_string in (select to_char(m.duplicate_site_id)
5157: from ra_customer_merges m
5158: where m.process_flag = 'N'
5159: and m.request_id = req_id
5160: and m.set_number = set_num)
5161: and column_name = 'SHIP_TO_ORG_ID'
5164: CURSOR c2 is
5165: select VALIDATION_TMPLT_ID
5166: from oe_pc_vtmplt_cols
5167: where value_string in (select to_char(m.duplicate_site_id)
5168: from ra_customer_merges m
5169: where m.process_flag = 'N'
5170: and m.request_id = req_id
5171: and m.set_number = set_num)
5172: and column_name = 'INVOICE_TO_ORG_ID'
5175: CURSOR c4 is
5176: select VALIDATION_TMPLT_ID
5177: from oe_pc_vtmplt_cols
5178: where value_string in (select to_char(m.duplicate_site_id)
5179: from ra_customer_merges m
5180: where m.process_flag = 'N'
5181: and m.request_id = req_id
5182: and m.set_number = set_num)
5183: and column_name = 'INTMED_SHIP_TO_ORG_ID'
5186: CURSOR c3 is
5187: select VALIDATION_TMPLT_ID
5188: from oe_pc_vtmplt_cols
5189: where value_string in (select to_char(m.duplicate_id)
5190: from ra_customer_merges m
5191: where m.process_flag = 'N'
5192: and m.request_id = req_id
5193: and m.set_number = set_num)
5194: and column_name = 'SOLD_TO_ORG_ID'
5258: hz_utility_pub.LAST_UPDATE_LOGIN,
5259: hz_utility_pub.LAST_UPDATE_DATE,
5260: hz_utility_pub.LAST_UPDATED_BY
5261: from OE_PC_VTMPLT_COLS a,
5262: ra_customer_merges m
5263: where a.value_string = to_char(m.duplicate_site_id)
5264: and m.process_flag = 'N'
5265: and m.request_id = req_id
5266: and m.set_number = set_num
5273: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5274:
5275: UPDATE OE_PC_VTMPLT_COLS a
5276: set value_string = (select distinct to_char(m.customer_site_id)
5277: from ra_customer_merges m
5278: where a.value_string =
5279: to_char(m.duplicate_site_id)
5280:
5281: and m.request_id = req_id
5284: last_update_date = sysdate,
5285: last_updated_by = arp_standard.profile.user_id,
5286: last_update_login = arp_standard.profile.last_update_login
5287: where value_string in (select to_char(m.duplicate_site_id)
5288: from ra_customer_merges m
5289: where m.process_flag = 'N'
5290: and m.request_id = req_id
5291: and m.set_number = set_num)
5292: and column_name = 'SHIP_TO_ORG_ID';
5326: hz_utility_pub.LAST_UPDATE_LOGIN,
5327: hz_utility_pub.LAST_UPDATE_DATE,
5328: hz_utility_pub.LAST_UPDATED_BY
5329: from OE_PC_VTMPLT_COLS a,
5330: ra_customer_merges m
5331: where a.value_string = to_char(m.duplicate_site_id)
5332: and m.process_flag = 'N'
5333: and m.request_id = req_id
5334: and m.set_number = set_num
5341: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5342:
5343: UPDATE OE_PC_VTMPLT_COLS a
5344: set value_string = (select distinct to_char(m.customer_site_id)
5345: from ra_customer_merges m
5346: where a.value_string =
5347: to_char(m.duplicate_site_id)
5348:
5349: and m.request_id = req_id
5352: last_update_date = sysdate,
5353: last_updated_by = arp_standard.profile.user_id,
5354: last_update_login = arp_standard.profile.last_update_login
5355: where value_string in (select to_char(m.duplicate_site_id)
5356: from ra_customer_merges m
5357: where m.process_flag = 'N'
5358: and m.request_id = req_id
5359: and m.set_number = set_num)
5360: and column_name = 'INVOICE_TO_ORG_ID';
5394: hz_utility_pub.LAST_UPDATE_LOGIN,
5395: hz_utility_pub.LAST_UPDATE_DATE,
5396: hz_utility_pub.LAST_UPDATED_BY
5397: from OE_PC_VTMPLT_COLS a,
5398: ra_customer_merges m
5399: where a.value_string = to_char(m.duplicate_site_id)
5400: and m.process_flag = 'N'
5401: and m.request_id = req_id
5402: and m.set_number = set_num
5409: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5410:
5411: UPDATE OE_PC_VTMPLT_COLS a
5412: set value_string = (select distinct to_char(m.customer_site_id)
5413: from ra_customer_merges m
5414: where a.value_string =
5415: to_char(m.duplicate_site_id)
5416:
5417: and m.request_id = req_id
5420: last_update_date = sysdate,
5421: last_updated_by = arp_standard.profile.user_id,
5422: last_update_login = arp_standard.profile.last_update_login
5423: where value_string in (select to_char(m.duplicate_site_id)
5424: from ra_customer_merges m
5425: where m.process_flag = 'N'
5426: and m.request_id = req_id
5427: and m.set_number = set_num)
5428: and column_name = 'INTMED_SHIP_TO_ORG_ID';
5463: hz_utility_pub.LAST_UPDATE_LOGIN,
5464: hz_utility_pub.LAST_UPDATE_DATE,
5465: hz_utility_pub.LAST_UPDATED_BY
5466: from OE_PC_VTMPLT_COLS a,
5467: ra_customer_merges m
5468: where a.value_string = to_char(m.duplicate_id)
5469: and m.process_flag = 'N'
5470: and m.request_id = req_id
5471: and m.set_number = set_num
5478: arp_message.set_token( 'TABLE_NAME', 'OE_PC_VTMPLT_COLS', FALSE );
5479:
5480: UPDATE OE_PC_VTMPLT_COLS a
5481: set value_string = (select distinct to_char(m.customer_id)
5482: from ra_customer_merges m
5483: where a.value_string =
5484: to_char(m.duplicate_id)
5485:
5486: and m.request_id = req_id
5489: last_update_date = sysdate,
5490: last_updated_by = arp_standard.profile.user_id,
5491: last_update_login = arp_standard.profile.last_update_login
5492: where value_string in (select to_char(m.duplicate_id)
5493: from ra_customer_merges m
5494: where m.process_flag = 'N'
5495: and m.request_id = req_id
5496: and m.set_number = set_num)
5497: and column_name = 'SOLD_TO_ORG_ID';
5525: CURSOR c1 is
5526: select hold_source_id
5527: from oe_hold_sources
5528: where hold_entity_id in (select m.duplicate_site_id
5529: from ra_customer_merges m
5530: where m.process_flag = 'N'
5531: and m.request_id = req_id
5532: and m.set_number = set_num)
5533: and hold_entity_code = 'S'
5536: CURSOR c2 is
5537: select hold_source_id
5538: from oe_hold_sources
5539: where hold_entity_id in (select m.duplicate_site_id
5540: from ra_customer_merges m
5541: where m.process_flag = 'N'
5542: and m.request_id = req_id
5543: and m.set_number = set_num)
5544: and hold_entity_code = 'B'
5548: CURSOR c3 is
5549: select hold_source_id
5550: from oe_hold_sources
5551: where hold_entity_id in (select m.duplicate_id
5552: from ra_customer_merges m
5553: where m.process_flag = 'N'
5554: and m.request_id = req_id
5555: and m.set_number = set_num)
5556: and hold_entity_code = 'C'
5615: hz_utility_pub.LAST_UPDATE_LOGIN,
5616: hz_utility_pub.LAST_UPDATE_DATE,
5617: hz_utility_pub.LAST_UPDATED_BY
5618: from OE_HOLD_SOURCES a,
5619: ra_customer_merges m
5620: where a.hold_entity_id = to_char(m.duplicate_site_id)
5621: and m.process_flag = 'N'
5622: and m.request_id = req_id
5623: and m.set_number = set_num
5630: arp_message.set_token( 'TABLE_NAME', 'OE_HOLD_SOURCES', FALSE );
5631:
5632: UPDATE OE_HOLD_SOURCES a
5633: set (hold_entity_id) = (select distinct to_char(m.customer_site_id) --Bug 8866783
5634: from ra_customer_merges m
5635: where a.hold_entity_id =
5636: m.duplicate_site_id
5637: and m.request_id = req_id
5638: and m.process_flag = 'N'
5641:
5642: last_updated_by = arp_standard.profile.user_id,
5643: last_update_login = arp_standard.profile.last_update_login
5644: where hold_entity_id in (select to_char(m.duplicate_site_id) --Bug 8866783
5645: from ra_customer_merges m
5646: where m.process_flag = 'N'
5647: and m.request_id = req_id
5648: and m.set_number = set_num)
5649: and hold_entity_code = 'S';
5683: hz_utility_pub.LAST_UPDATE_LOGIN,
5684: hz_utility_pub.LAST_UPDATE_DATE,
5685: hz_utility_pub.LAST_UPDATED_BY
5686: from OE_HOLD_SOURCES a,
5687: ra_customer_merges m
5688: where a.hold_entity_id = to_char(m.duplicate_site_id)
5689: and m.process_flag = 'N'
5690: and m.request_id = req_id
5691: and m.set_number = set_num
5699: oe_debug_pub.add('ER 3095805 Updating Bill To Site 1');
5700:
5701: UPDATE OE_HOLD_SOURCES a
5702: set (hold_entity_id) = (select distinct to_char(m.customer_site_id) --Bug 8866783
5703: from ra_customer_merges m
5704: where a.hold_entity_id =
5705:
5706: m.duplicate_site_id
5707: and m.request_id = req_id
5710: last_update_date = sysdate,
5711: last_updated_by = arp_standard.profile.user_id,
5712: last_update_login = arp_standard.profile.last_update_login
5713: where hold_entity_id in (select to_char(m.duplicate_site_id) --Bug 8866783
5714: from ra_customer_merges m
5715: where m.process_flag = 'N'
5716: and m.request_id = req_id
5717: and m.set_number = set_num)
5718: and hold_entity_code = 'B'
5754: hz_utility_pub.LAST_UPDATE_LOGIN,
5755: hz_utility_pub.LAST_UPDATE_DATE,
5756: hz_utility_pub.LAST_UPDATED_BY
5757: from OE_HOLD_SOURCES a,
5758: ra_customer_merges m
5759: where a.hold_entity_id = to_char(m.duplicate_id)
5760: and m.process_flag = 'N'
5761: and m.request_id = req_id
5762: and m.set_number = set_num
5770: oe_debug_pub.add('ER 3095805 Updating Acc');
5771:
5772: UPDATE OE_HOLD_SOURCES a
5773: set hold_entity_id = (select distinct to_char(m.customer_id) --Bug 8866783
5774: from ra_customer_merges m
5775: where a.hold_entity_id =
5776: m.duplicate_id
5777: and m.process_flag = 'N'
5778: and m.request_id = req_id
5780: last_update_date = sysdate,
5781: last_updated_by = arp_standard.profile.user_id,
5782: last_update_login = arp_standard.profile.last_update_login
5783: where hold_entity_id in (select to_char(m.duplicate_id) --Bug 8866783
5784: from ra_customer_merges m
5785: where m.process_flag = 'N'
5786: and m.request_id = req_id
5787: and m.set_number = set_num)
5788: and hold_entity_code = 'C'
5814: select DROP_SHIP_SOURCE_ID
5815: from oe_drop_ship_sources
5816: where LINE_LOCATION_ID in
5817: (select m.duplicate_site_id
5818: from ra_customer_merges m
5819: where m.process_flag = 'N'
5820: and m.request_id = req_id
5821: and m.set_number = set_num)
5822: for update nowait;
5871: hz_utility_pub.LAST_UPDATE_LOGIN,
5872: hz_utility_pub.LAST_UPDATE_DATE,
5873: hz_utility_pub.LAST_UPDATED_BY
5874: from OE_DROP_SHIP_SOURCES a,
5875: ra_customer_merges m
5876: where a.line_location_id = to_char(m.duplicate_site_id)
5877: and m.process_flag = 'N'
5878: and m.request_id = req_id
5879: and m.set_number = set_num;
5885: arp_message.set_token( 'TABLE_NAME', 'OE_DROP_SHIP_SOURCES', FALSE );
5886:
5887: UPDATE OE_DROP_SHIP_SOURCES a
5888: set line_location_id = (select distinct m.customer_site_id
5889: from ra_customer_merges m
5890: where a.line_location_id = m.duplicate_site_id
5891: and m.request_id = req_id
5892: and m.process_flag = 'N'
5893: and m.set_number = set_num),
5894: last_update_date = sysdate,
5895: last_updated_by = arp_standard.profile.user_id,
5896: last_update_login = arp_standard.profile.last_update_login
5897: where line_location_id in (select m.duplicate_site_id
5898: from ra_customer_merges m
5899: where m.process_flag = 'N'
5900: and m.request_id = req_id
5901: and m.set_number = set_num);
5902: g_count := sql%rowcount;
5922: select CUST_ITEM_SETTING_ID
5923: from oe_cust_item_settings
5924: where site_use_id in
5925: (select m.duplicate_site_id
5926: from ra_customer_merges m
5927: where m.process_flag = 'N'
5928: and m.request_id = req_id
5929: and m.set_number = set_num)
5930: for update nowait;
5933: select CUST_ITEM_SETTING_ID
5934: from oe_cust_item_settings
5935: where customer_id in
5936: (select m.duplicate_id
5937: from ra_customer_merges m
5938: where m.process_flag = 'N'
5939: and m.request_id = req_id
5940: and m.set_number = set_num)
5941: for update nowait;
5995: hz_utility_pub.LAST_UPDATE_LOGIN,
5996: hz_utility_pub.LAST_UPDATE_DATE,
5997: hz_utility_pub.LAST_UPDATED_BY
5998: from OE_CUST_ITEM_SETTINGS a,
5999: ra_customer_merges m
6000: where a.site_use_id = m.duplicate_site_id
6001: and m.process_flag = 'N'
6002: and m.request_id = req_id
6003: and m.set_number = set_num;
6009: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
6010:
6011: UPDATE OE_CUST_ITEM_SETTINGS a
6012: set site_use_id = (select distinct m.customer_site_id
6013: from ra_customer_merges m
6014: where a.site_use_id = m.duplicate_site_id
6015: and m.request_id = req_id
6016: and m.process_flag = 'N'
6017: and m.set_number = set_num),
6018: last_update_date = sysdate,
6019: last_updated_by = arp_standard.profile.user_id,
6020: last_update_login = arp_standard.profile.last_update_login
6021: where site_use_id in (select m.duplicate_site_id
6022: from ra_customer_merges m
6023: where m.process_flag = 'N'
6024: and m.request_id = req_id
6025: and m.set_number = set_num);
6026: g_count := sql%rowcount;
6057: hz_utility_pub.LAST_UPDATE_LOGIN,
6058: hz_utility_pub.LAST_UPDATE_DATE,
6059: hz_utility_pub.LAST_UPDATED_BY
6060: from OE_CUST_ITEM_SETTINGS a,
6061: ra_customer_merges m
6062: where a.customer_id = m.duplicate_id
6063: and m.process_flag = 'N'
6064: and m.request_id = req_id
6065: and m.set_number = set_num;
6071: arp_message.set_token( 'TABLE_NAME', 'OE_CUST_ITEM_SETTINGS', FALSE );
6072:
6073: UPDATE OE_CUST_ITEM_SETTINGS a
6074: set customer_id = (select distinct m.customer_id
6075: from ra_customer_merges m
6076: where a.customer_id = m.duplicate_id
6077: and m.request_id = req_id
6078: and m.process_flag = 'N'
6079: and m.set_number = set_num),
6080: last_update_date = sysdate,
6081: last_updated_by = arp_standard.profile.user_id,
6082: last_update_login = arp_standard.profile.last_update_login
6083: where customer_id in (select m.duplicate_id
6084: from ra_customer_merges m
6085: where m.process_flag = 'N'
6086: and m.request_id = req_id
6087: and m.set_number = set_num);
6088: g_count := sql%rowcount;
6111: PARALLEL(S,30)
6112: PARALLEL(M,30)
6113: USE_HASH(M,S) */
6114: s.set_id , s.ship_to_org_id , m.customer_merge_id
6115: FROM RA_CUSTOMER_MERGES M, OE_SETS S
6116: WHERE
6117: s.ship_to_org_id = m.duplicate_site_id
6118: and m.process_flag = 'N'
6119: and m.request_id = req_id
6126: PARALLEL(S,30)
6127: PARALLEL(M,30)
6128: USE_HASH(M,S) */
6129: s.set_id , s.ship_to_org_id ,m.customer_merge_id
6130: FROM RA_CUSTOMER_MERGES M, OE_SETS S
6131: WHERE
6132: s.ship_to_org_id = m.duplicate_site_id
6133: and m.process_flag = 'N'
6134: and m.request_id = req_id
6138: CURSOR SETS_MERGE_2_NP IS
6139: SELECT s.set_id ,
6140: s.ship_to_org_id ,
6141: m.customer_merge_id
6142: FROM RA_CUSTOMER_MERGES M, OE_SETS S
6143: WHERE
6144: s.ship_to_org_id = m.duplicate_site_id
6145: and m.process_flag = 'N'
6146: and m.request_id = req_id
6288: decode(a.pricing_attribute, 'QUALIFIER_ATTRIBUTE2',
6289: m.customer_id, m.customer_site_id) attrib_value_from, a.pricing_attr_value_from,
6290: m.customer_merge_id
6291: --changed for bug 3196900
6292: -- FROM RA_CUSTOMER_MERGES M, OE_PRICE_ADJ_ATTRIBS A
6293: FROM RA_CUSTOMER_MERGES M, OE_PRICE_ADJ_ATTRIBS A,OE_PRICE_ADJUSTMENTS P,OE_ORDER_HEADERS H
6294: WHERE
6295: a.pricing_context = 'CUSTOMER'
6296: and ((a.pricing_attribute in ('QUALIFIER_ATTRIBUTE11',
6289: m.customer_id, m.customer_site_id) attrib_value_from, a.pricing_attr_value_from,
6290: m.customer_merge_id
6291: --changed for bug 3196900
6292: -- FROM RA_CUSTOMER_MERGES M, OE_PRICE_ADJ_ATTRIBS A
6293: FROM RA_CUSTOMER_MERGES M, OE_PRICE_ADJ_ATTRIBS A,OE_PRICE_ADJUSTMENTS P,OE_ORDER_HEADERS H
6294: WHERE
6295: a.pricing_context = 'CUSTOMER'
6296: and ((a.pricing_attribute in ('QUALIFIER_ATTRIBUTE11',
6297: 'QUALIFIER_ATTRIBUTE5',
6315: decode(a.pricing_attribute, 'QUALIFIER_ATTRIBUTE2',
6316: m.customer_id, m.customer_site_id) attrib_value_from, a.pricing_attr_value_from,
6317: m.customer_merge_id
6318: --changed for bug 3196900
6319: -- FROM RA_CUSTOMER_MERGES M, OE_PRICE_ADJ_ATTRIBS A
6320: FROM RA_CUSTOMER_MERGES M, OE_PRICE_ADJ_ATTRIBS A , OE_PRICE_ADJUSTMENTS P,
6321: OE_ORDER_HEADERS H
6322: WHERE
6323: a.pricing_context = 'CUSTOMER'
6316: m.customer_id, m.customer_site_id) attrib_value_from, a.pricing_attr_value_from,
6317: m.customer_merge_id
6318: --changed for bug 3196900
6319: -- FROM RA_CUSTOMER_MERGES M, OE_PRICE_ADJ_ATTRIBS A
6320: FROM RA_CUSTOMER_MERGES M, OE_PRICE_ADJ_ATTRIBS A , OE_PRICE_ADJUSTMENTS P,
6321: OE_ORDER_HEADERS H
6322: WHERE
6323: a.pricing_context = 'CUSTOMER'
6324: and ((a.pricing_attribute in ('QUALIFIER_ATTRIBUTE11',
6454: IS
6455:
6456: CURSOR MERGE_SITES IS
6457: select duplicate_id, customer_id, duplicate_site_id, customer_site_id
6458: from ra_customer_merges m
6459: where m.process_flag = 'N'
6460: and m.request_id = req_id
6461: and m.set_number = set_num;
6462:
6474: h.ship_to_org_id,
6475: h.deliver_to_org_id,
6476: h.sold_to_site_use_id,
6477: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
6478: FROM RA_CUSTOMER_MERGES M1,
6479: RA_CUSTOMER_MERGES M2,
6480: RA_CUSTOMER_MERGES M3,
6481: RA_CUSTOMER_MERGES M4,
6482: RA_CUSTOMER_MERGES M5,
6475: h.deliver_to_org_id,
6476: h.sold_to_site_use_id,
6477: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
6478: FROM RA_CUSTOMER_MERGES M1,
6479: RA_CUSTOMER_MERGES M2,
6480: RA_CUSTOMER_MERGES M3,
6481: RA_CUSTOMER_MERGES M4,
6482: RA_CUSTOMER_MERGES M5,
6483: -- changed for bug 3196900
6476: h.sold_to_site_use_id,
6477: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
6478: FROM RA_CUSTOMER_MERGES M1,
6479: RA_CUSTOMER_MERGES M2,
6480: RA_CUSTOMER_MERGES M3,
6481: RA_CUSTOMER_MERGES M4,
6482: RA_CUSTOMER_MERGES M5,
6483: -- changed for bug 3196900
6484: -- OE_BLANKET_HEADERS_ALL H
6477: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
6478: FROM RA_CUSTOMER_MERGES M1,
6479: RA_CUSTOMER_MERGES M2,
6480: RA_CUSTOMER_MERGES M3,
6481: RA_CUSTOMER_MERGES M4,
6482: RA_CUSTOMER_MERGES M5,
6483: -- changed for bug 3196900
6484: -- OE_BLANKET_HEADERS_ALL H
6485: OE_BLANKET_HEADERS H
6478: FROM RA_CUSTOMER_MERGES M1,
6479: RA_CUSTOMER_MERGES M2,
6480: RA_CUSTOMER_MERGES M3,
6481: RA_CUSTOMER_MERGES M4,
6482: RA_CUSTOMER_MERGES M5,
6483: -- changed for bug 3196900
6484: -- OE_BLANKET_HEADERS_ALL H
6485: OE_BLANKET_HEADERS H
6486: WHERE
6526: h.ship_to_org_id,
6527: h.deliver_to_org_id,
6528: h.sold_to_site_use_id,
6529: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
6530: FROM RA_CUSTOMER_MERGES M1,
6531: RA_CUSTOMER_MERGES M2,
6532: RA_CUSTOMER_MERGES M3,
6533: RA_CUSTOMER_MERGES M4,
6534: RA_CUSTOMER_MERGES M5,
6527: h.deliver_to_org_id,
6528: h.sold_to_site_use_id,
6529: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
6530: FROM RA_CUSTOMER_MERGES M1,
6531: RA_CUSTOMER_MERGES M2,
6532: RA_CUSTOMER_MERGES M3,
6533: RA_CUSTOMER_MERGES M4,
6534: RA_CUSTOMER_MERGES M5,
6535: -- changed for bug 3196900
6528: h.sold_to_site_use_id,
6529: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
6530: FROM RA_CUSTOMER_MERGES M1,
6531: RA_CUSTOMER_MERGES M2,
6532: RA_CUSTOMER_MERGES M3,
6533: RA_CUSTOMER_MERGES M4,
6534: RA_CUSTOMER_MERGES M5,
6535: -- changed for bug 3196900
6536: -- OE_BLANKET_HEADERS_ALL H
6529: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
6530: FROM RA_CUSTOMER_MERGES M1,
6531: RA_CUSTOMER_MERGES M2,
6532: RA_CUSTOMER_MERGES M3,
6533: RA_CUSTOMER_MERGES M4,
6534: RA_CUSTOMER_MERGES M5,
6535: -- changed for bug 3196900
6536: -- OE_BLANKET_HEADERS_ALL H
6537: OE_BLANKET_HEADERS H
6530: FROM RA_CUSTOMER_MERGES M1,
6531: RA_CUSTOMER_MERGES M2,
6532: RA_CUSTOMER_MERGES M3,
6533: RA_CUSTOMER_MERGES M4,
6534: RA_CUSTOMER_MERGES M5,
6535: -- changed for bug 3196900
6536: -- OE_BLANKET_HEADERS_ALL H
6537: OE_BLANKET_HEADERS H
6538: WHERE
6579: H.HEADER_ID, H.SOLD_TO_ORG_ID, H.INVOICE_TO_ORG_ID,
6580: H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
6581: H.SOLD_TO_SITE_USE_ID, M1.CUSTOMER_MERGE_ID
6582: FROM
6583: RA_CUSTOMER_MERGES M1, OE_BLANKET_HEADERS H
6584: WHERE H.INVOICE_TO_ORG_ID= M1.DUPLICATE_SITE_ID
6585: AND M1.PROCESS_FLAG='N'
6586: AND M1.REQUEST_ID = req_id
6587: AND M1.SET_NUMBER = set_num
6590: H.HEADER_ID, H.SOLD_TO_ORG_ID, H.INVOICE_TO_ORG_ID,
6591: H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
6592: H.SOLD_TO_SITE_USE_ID, M1.CUSTOMER_MERGE_ID
6593: FROM
6594: RA_CUSTOMER_MERGES M1, OE_BLANKET_HEADERS H
6595: WHERE H.SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
6596: AND M1.PROCESS_FLAG='N'
6597: AND M1.REQUEST_ID = req_id
6598: AND M1.SET_NUMBER = set_num
6601: H.HEADER_ID, H.SOLD_TO_ORG_ID, H.INVOICE_TO_ORG_ID,
6602: H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
6603: H.SOLD_TO_SITE_USE_ID, M1.CUSTOMER_MERGE_ID
6604: FROM
6605: RA_CUSTOMER_MERGES M1, OE_BLANKET_HEADERS H
6606: WHERE H.SOLD_TO_SITE_USE_ID= M1.DUPLICATE_SITE_ID
6607: AND M1.PROCESS_FLAG='N'
6608: AND M1.REQUEST_ID = req_id
6609: AND M1.SET_NUMBER = set_num
6612: H.HEADER_ID, H.SOLD_TO_ORG_ID, H.INVOICE_TO_ORG_ID,
6613: H.SHIP_TO_ORG_ID, H.DELIVER_TO_ORG_ID,
6614: H.SOLD_TO_SITE_USE_ID, M1.CUSTOMER_MERGE_ID
6615: FROM
6616: RA_CUSTOMER_MERGES M1, OE_BLANKET_HEADERS H
6617: WHERE H.DELIVER_TO_ORG_ID= M1.DUPLICATE_SITE_ID
6618: AND M1.PROCESS_FLAG='N'
6619: AND M1.REQUEST_ID = req_id
6620: AND M1.SET_NUMBER = set_num;
6876: select hist.HEADER_ID
6877: from oe_blanket_headers_hist hist,oe_blanket_headers h
6878: where hist.ship_to_org_id in
6879: (select m.duplicate_site_id
6880: from ra_customer_merges m
6881: where m.process_flag = 'N'
6882: and m.request_id = req_id
6883: and m.set_number = set_num)
6884: --added for bug 3196900
6891: select hist.HEADER_ID
6892: from oe_blanket_headers_hist hist,oe_blanket_headers h
6893: where hist.invoice_to_org_id in
6894: (select m.duplicate_site_id
6895: from ra_customer_merges m
6896: where m.process_flag = 'N'
6897: and m.request_id = req_id
6898: and m.set_number = set_num)
6899: --changed for bug 3196900
6906: select hist.HEADER_ID
6907: from oe_blanket_headers_hist hist, oe_blanket_headers h
6908: where hist.deliver_to_org_id in
6909: (select m.duplicate_site_id
6910: from ra_customer_merges m
6911: where m.process_flag = 'N'
6912: and m.request_id = req_id
6913: and m.set_number = set_num)
6914: --added for bug 3196900
6921: select hist.HEADER_ID
6922: from oe_blanket_headers_hist hist, oe_blanket_headers h
6923: where hist.sold_to_org_id in
6924: (select m.duplicate_id
6925: from ra_customer_merges m
6926: where m.process_flag = 'N'
6927: and m.request_id = req_id
6928: and m.set_number = set_num)
6929: --added for bug 3196900
6935: select hist.HEADER_ID
6936: from oe_blanket_headers_hist hist,oe_blanket_headers h
6937: where hist.sold_to_site_use_id in
6938: (select m.duplicate_id
6939: from ra_customer_merges m
6940: where m.process_flag = 'N'
6941: and m.request_id = req_id
6942: and m.set_number = set_num)
6943: --added for bug 3196900
7008: hz_utility_pub.LAST_UPDATE_LOGIN,
7009: hz_utility_pub.LAST_UPDATE_DATE,
7010: hz_utility_pub.LAST_UPDATED_BY
7011: from oe_blanket_headers_hist h,
7012: ra_customer_merges m
7013: where h.ship_to_org_id=m.duplicate_site_id
7014: and m.process_flag = 'N'
7015: and m.request_id = req_id
7016: and m.set_number = set_num;
7021: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7022:
7023: UPDATE oe_blanket_headers_hist a
7024: set ship_to_org_id = (select distinct m.customer_site_id
7025: from ra_customer_merges m
7026: where a.ship_to_org_id = m.duplicate_site_id
7027: and m.request_id = req_id
7028: and m.process_flag = 'N'
7029: and m.set_number = set_num),
7034: program_application_id =arp_standard.profile.program_application_id,
7035: program_id = arp_standard.profile.program_id,
7036: program_update_date = sysdate
7037: where ship_to_org_id in (select m.duplicate_site_id
7038: from ra_customer_merges m
7039: where m.process_flag = 'N'
7040: and m.request_id = req_id
7041: and m.set_number = set_num);
7042: g_count := sql%rowcount;
7075: hz_utility_pub.LAST_UPDATE_LOGIN,
7076: hz_utility_pub.LAST_UPDATE_DATE,
7077: hz_utility_pub.LAST_UPDATED_BY
7078: from oe_blanket_headers_hist h,
7079: ra_customer_merges m
7080: where h.invoice_to_org_id=m.duplicate_site_id
7081: and m.process_flag = 'N'
7082: and m.request_id = req_id
7083: and m.set_number = set_num;
7087: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7088:
7089: UPDATE oe_blanket_headers_hist a
7090: set invoice_to_org_id = (select distinct m.customer_site_id
7091: from ra_customer_merges m
7092: where a.invoice_to_org_id = m.duplicate_site_id
7093: and m.request_id = req_id
7094: and m.process_flag = 'N'
7095: and m.set_number = set_num),
7100: program_application_id =arp_standard.profile.program_application_id,
7101: program_id = arp_standard.profile.program_id,
7102: program_update_date = sysdate
7103: where invoice_to_org_id in (select m.duplicate_site_id
7104: from ra_customer_merges m
7105: where m.process_flag = 'N'
7106: and m.request_id = req_id
7107: and m.set_number = set_num);
7108: g_count := sql%rowcount;
7142: hz_utility_pub.LAST_UPDATE_LOGIN,
7143: hz_utility_pub.LAST_UPDATE_DATE,
7144: hz_utility_pub.LAST_UPDATED_BY
7145: from oe_blanket_headers_hist h,
7146: ra_customer_merges m
7147: where h.deliver_to_org_id=m.duplicate_site_id
7148: and m.process_flag = 'N'
7149: and m.request_id = req_id
7150: and m.set_number = set_num;
7153: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7154:
7155: UPDATE oe_blanket_headers_hist a
7156: set deliver_to_org_id = (select distinct m.customer_site_id
7157: from ra_customer_merges m
7158: where a.deliver_to_org_id = m.duplicate_site_id
7159: and m.request_id = req_id
7160: and m.process_flag = 'N'
7161: and m.set_number = set_num),
7166: program_application_id =arp_standard.profile.program_application_id,
7167: program_id = arp_standard.profile.program_id,
7168: program_update_date = sysdate
7169: where deliver_to_org_id in (select m.duplicate_site_id
7170: from ra_customer_merges m
7171: where m.process_flag = 'N'
7172: and m.request_id = req_id
7173: and m.set_number = set_num);
7174: g_count := sql%rowcount;
7211: hz_utility_pub.LAST_UPDATE_LOGIN,
7212: hz_utility_pub.LAST_UPDATE_DATE,
7213: hz_utility_pub.LAST_UPDATED_BY
7214: from oe_blanket_headers_hist h,
7215: ra_customer_merges m
7216: where h.sold_to_org_id=m.duplicate_site_id
7217: and m.process_flag = 'N'
7218: and m.request_id = req_id
7219: and m.set_number = set_num;
7224: arp_message.set_token( 'TABLE_NAME', 'oe_blanket_headers_hist', FALSE );
7225:
7226: UPDATE oe_blanket_headers_hist a
7227: set sold_to_org_id = (select distinct m.customer_id
7228: from ra_customer_merges m
7229: where a.sold_to_org_id = m.duplicate_id
7230: and m.process_flag = 'N'
7231: and m.request_id = req_id
7232: and m.set_number = set_num),
7237: program_application_id =arp_standard.profile.program_application_id,
7238: program_id = arp_standard.profile.program_id,
7239: program_update_date = sysdate
7240: where sold_to_org_id in (select m.duplicate_id
7241: from ra_customer_merges m
7242: where m.process_flag = 'N'
7243: and m.request_id = req_id
7244: and m.set_number = set_num);
7245:
7284: hz_utility_pub.LAST_UPDATE_LOGIN,
7285: hz_utility_pub.LAST_UPDATE_DATE,
7286: hz_utility_pub.LAST_UPDATED_BY
7287: from oe_blanket_headers_hist h,
7288: ra_customer_merges m
7289: where h.sold_to_org_id=m.duplicate_site_id
7290: and m.process_flag = 'N'
7291: and m.request_id = req_id
7292: and m.set_number = set_num;
7297: arp_message.set_token( 'TABLE_NAME', 'OE_BLANKET_HEADERS_HIST', FALSE );
7298:
7299: UPDATE oe_blanket_headers_hist a
7300: set sold_to_site_use_id = (select distinct m.customer_id
7301: from ra_customer_merges m
7302: where a.sold_to_site_use_id = m.duplicate_id
7303: and m.process_flag = 'N'
7304: and m.request_id = req_id
7305: and m.set_number = set_num),
7310: program_application_id =arp_standard.profile.program_application_id,
7311: program_id = arp_standard.profile.program_id,
7312: program_update_date = sysdate
7313: where sold_to_site_use_id in (select m.duplicate_id
7314: from ra_customer_merges m
7315: where m.process_flag = 'N'
7316: and m.request_id = req_id
7317: and m.set_number = set_num);
7318:
7342: IS
7343:
7344: CURSOR MERGE_SITES IS
7345: select duplicate_id, customer_id, duplicate_site_id, customer_site_id
7346: from ra_customer_merges m
7347: where m.process_flag = 'N'
7348: and m.request_id = req_id
7349: and m.set_number = set_num;
7350:
7361: ship_to_org_id,
7362: intmed_ship_to_org_id,
7363: deliver_to_org_id,
7364: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7365: FROM RA_CUSTOMER_MERGES M1,
7366: RA_CUSTOMER_MERGES M2,
7367: RA_CUSTOMER_MERGES M3,
7368: RA_CUSTOMER_MERGES M4,
7369: RA_CUSTOMER_MERGES M5,
7362: intmed_ship_to_org_id,
7363: deliver_to_org_id,
7364: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7365: FROM RA_CUSTOMER_MERGES M1,
7366: RA_CUSTOMER_MERGES M2,
7367: RA_CUSTOMER_MERGES M3,
7368: RA_CUSTOMER_MERGES M4,
7369: RA_CUSTOMER_MERGES M5,
7370: -- changed for bug 3196900
7363: deliver_to_org_id,
7364: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7365: FROM RA_CUSTOMER_MERGES M1,
7366: RA_CUSTOMER_MERGES M2,
7367: RA_CUSTOMER_MERGES M3,
7368: RA_CUSTOMER_MERGES M4,
7369: RA_CUSTOMER_MERGES M5,
7370: -- changed for bug 3196900
7371: -- OE_BLANKET_LINES_ALL L
7364: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7365: FROM RA_CUSTOMER_MERGES M1,
7366: RA_CUSTOMER_MERGES M2,
7367: RA_CUSTOMER_MERGES M3,
7368: RA_CUSTOMER_MERGES M4,
7369: RA_CUSTOMER_MERGES M5,
7370: -- changed for bug 3196900
7371: -- OE_BLANKET_LINES_ALL L
7372: OE_BLANKET_LINES L
7365: FROM RA_CUSTOMER_MERGES M1,
7366: RA_CUSTOMER_MERGES M2,
7367: RA_CUSTOMER_MERGES M3,
7368: RA_CUSTOMER_MERGES M4,
7369: RA_CUSTOMER_MERGES M5,
7370: -- changed for bug 3196900
7371: -- OE_BLANKET_LINES_ALL L
7372: OE_BLANKET_LINES L
7373: WHERE
7413: ship_to_org_id,
7414: intmed_ship_to_org_id,
7415: deliver_to_org_id,
7416: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7417: FROM RA_CUSTOMER_MERGES M1,
7418: RA_CUSTOMER_MERGES M2,
7419: RA_CUSTOMER_MERGES M3,
7420: RA_CUSTOMER_MERGES M4,
7421: RA_CUSTOMER_MERGES M5,
7414: intmed_ship_to_org_id,
7415: deliver_to_org_id,
7416: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7417: FROM RA_CUSTOMER_MERGES M1,
7418: RA_CUSTOMER_MERGES M2,
7419: RA_CUSTOMER_MERGES M3,
7420: RA_CUSTOMER_MERGES M4,
7421: RA_CUSTOMER_MERGES M5,
7422: --changed for bug 3196900
7415: deliver_to_org_id,
7416: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7417: FROM RA_CUSTOMER_MERGES M1,
7418: RA_CUSTOMER_MERGES M2,
7419: RA_CUSTOMER_MERGES M3,
7420: RA_CUSTOMER_MERGES M4,
7421: RA_CUSTOMER_MERGES M5,
7422: --changed for bug 3196900
7423: -- OE_BLANKET_LINES_ALL L
7416: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7417: FROM RA_CUSTOMER_MERGES M1,
7418: RA_CUSTOMER_MERGES M2,
7419: RA_CUSTOMER_MERGES M3,
7420: RA_CUSTOMER_MERGES M4,
7421: RA_CUSTOMER_MERGES M5,
7422: --changed for bug 3196900
7423: -- OE_BLANKET_LINES_ALL L
7424: OE_BLANKET_LINES L
7417: FROM RA_CUSTOMER_MERGES M1,
7418: RA_CUSTOMER_MERGES M2,
7419: RA_CUSTOMER_MERGES M3,
7420: RA_CUSTOMER_MERGES M4,
7421: RA_CUSTOMER_MERGES M5,
7422: --changed for bug 3196900
7423: -- OE_BLANKET_LINES_ALL L
7424: OE_BLANKET_LINES L
7425: WHERE
7469: INVOICE_TO_ORG_ID, SHIP_TO_ORG_ID,
7470: INTMED_SHIP_TO_ORG_ID, DELIVER_TO_ORG_ID,
7471: M1.CUSTOMER_MERGE_ID
7472: FROM
7473: RA_CUSTOMER_MERGES M1, OE_BLANKET_LINES L
7474: WHERE L.INVOICE_TO_ORG_ID= M1.DUPLICATE_SITE_ID
7475: AND M1.PROCESS_FLAG='N'
7476: AND M1.REQUEST_ID = req_id
7477: AND M1.SET_NUMBER = set_num
7481: INVOICE_TO_ORG_ID, SHIP_TO_ORG_ID,
7482: INTMED_SHIP_TO_ORG_ID, DELIVER_TO_ORG_ID,
7483: M1.CUSTOMER_MERGE_ID
7484: FROM
7485: RA_CUSTOMER_MERGES M1, OE_BLANKET_LINES L
7486: WHERE L.SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
7487: AND M1.PROCESS_FLAG='N'
7488: AND M1.REQUEST_ID = req_id
7489: AND M1.SET_NUMBER = set_num
7493: INVOICE_TO_ORG_ID, SHIP_TO_ORG_ID,
7494: INTMED_SHIP_TO_ORG_ID, DELIVER_TO_ORG_ID,
7495: M1.CUSTOMER_MERGE_ID
7496: FROM
7497: RA_CUSTOMER_MERGES M1, OE_BLANKET_LINES L
7498: WHERE L.INTMED_SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
7499: AND M1.PROCESS_FLAG='N'
7500: AND M1.REQUEST_ID = req_id
7501: AND M1.SET_NUMBER = set_num
7505: INVOICE_TO_ORG_ID, SHIP_TO_ORG_ID,
7506: INTMED_SHIP_TO_ORG_ID, DELIVER_TO_ORG_ID,
7507: M1.CUSTOMER_MERGE_ID
7508: FROM
7509: RA_CUSTOMER_MERGES M1, OE_BLANKET_LINES L
7510: WHERE L.DELIVER_TO_ORG_ID= M1.DUPLICATE_SITE_ID
7511: AND M1.PROCESS_FLAG='N'
7512: AND M1.REQUEST_ID = req_id
7513: AND M1.SET_NUMBER = set_num;
7764: IS
7765:
7766: CURSOR MERGE_SITES IS
7767: select duplicate_id, customer_id, duplicate_site_id, customer_site_id
7768: from ra_customer_merges m
7769: where m.process_flag = 'N'
7770: and m.request_id = req_id
7771: and m.set_number = set_num;
7772: /* MOAC_SQL_CHANGE */
7785: l.intmed_ship_to_org_id,
7786: l.deliver_to_org_id,
7787: l.version_number,
7788: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7789: FROM RA_CUSTOMER_MERGES M1,
7790: RA_CUSTOMER_MERGES M2,
7791: RA_CUSTOMER_MERGES M3,
7792: RA_CUSTOMER_MERGES M4,
7793: RA_CUSTOMER_MERGES M5,
7786: l.deliver_to_org_id,
7787: l.version_number,
7788: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7789: FROM RA_CUSTOMER_MERGES M1,
7790: RA_CUSTOMER_MERGES M2,
7791: RA_CUSTOMER_MERGES M3,
7792: RA_CUSTOMER_MERGES M4,
7793: RA_CUSTOMER_MERGES M5,
7794: --changed for bug 3196900
7787: l.version_number,
7788: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7789: FROM RA_CUSTOMER_MERGES M1,
7790: RA_CUSTOMER_MERGES M2,
7791: RA_CUSTOMER_MERGES M3,
7792: RA_CUSTOMER_MERGES M4,
7793: RA_CUSTOMER_MERGES M5,
7794: --changed for bug 3196900
7795: -- OE_BLANKET_LINES_HIST L
7788: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7789: FROM RA_CUSTOMER_MERGES M1,
7790: RA_CUSTOMER_MERGES M2,
7791: RA_CUSTOMER_MERGES M3,
7792: RA_CUSTOMER_MERGES M4,
7793: RA_CUSTOMER_MERGES M5,
7794: --changed for bug 3196900
7795: -- OE_BLANKET_LINES_HIST L
7796: OE_BLANKET_LINES_HIST L , OE_BLANKET_LINES_ALL LN
7789: FROM RA_CUSTOMER_MERGES M1,
7790: RA_CUSTOMER_MERGES M2,
7791: RA_CUSTOMER_MERGES M3,
7792: RA_CUSTOMER_MERGES M4,
7793: RA_CUSTOMER_MERGES M5,
7794: --changed for bug 3196900
7795: -- OE_BLANKET_LINES_HIST L
7796: OE_BLANKET_LINES_HIST L , OE_BLANKET_LINES_ALL LN
7797: -- Changed for MOAC
7842: l.intmed_ship_to_org_id,
7843: l.deliver_to_org_id,
7844: l.version_number,
7845: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7846: FROM RA_CUSTOMER_MERGES M1,
7847: RA_CUSTOMER_MERGES M2,
7848: RA_CUSTOMER_MERGES M3,
7849: RA_CUSTOMER_MERGES M4,
7850: RA_CUSTOMER_MERGES M5,
7843: l.deliver_to_org_id,
7844: l.version_number,
7845: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7846: FROM RA_CUSTOMER_MERGES M1,
7847: RA_CUSTOMER_MERGES M2,
7848: RA_CUSTOMER_MERGES M3,
7849: RA_CUSTOMER_MERGES M4,
7850: RA_CUSTOMER_MERGES M5,
7851: --changed for bug 3196900
7844: l.version_number,
7845: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7846: FROM RA_CUSTOMER_MERGES M1,
7847: RA_CUSTOMER_MERGES M2,
7848: RA_CUSTOMER_MERGES M3,
7849: RA_CUSTOMER_MERGES M4,
7850: RA_CUSTOMER_MERGES M5,
7851: --changed for bug 3196900
7852: -- OE_BLANKET_LINES_HIST L
7845: nvl(m1.customer_merge_id,nvl(m2.customer_merge_id,nvl(m3.customer_merge_id,nvl(m4.customer_merge_id,m5.customer_merge_id))))
7846: FROM RA_CUSTOMER_MERGES M1,
7847: RA_CUSTOMER_MERGES M2,
7848: RA_CUSTOMER_MERGES M3,
7849: RA_CUSTOMER_MERGES M4,
7850: RA_CUSTOMER_MERGES M5,
7851: --changed for bug 3196900
7852: -- OE_BLANKET_LINES_HIST L
7853: OE_BLANKET_LINES_HIST L , OE_BLANKET_LINES_all LN
7846: FROM RA_CUSTOMER_MERGES M1,
7847: RA_CUSTOMER_MERGES M2,
7848: RA_CUSTOMER_MERGES M3,
7849: RA_CUSTOMER_MERGES M4,
7850: RA_CUSTOMER_MERGES M5,
7851: --changed for bug 3196900
7852: -- OE_BLANKET_LINES_HIST L
7853: OE_BLANKET_LINES_HIST L , OE_BLANKET_LINES_all LN
7854: -- Changed for MOAC
7898: SELECT L.LINE_ID , L.SOLD_TO_ORG_ID, L.INVOICE_TO_ORG_ID,
7899: L.SHIP_TO_ORG_ID, L.INTMED_SHIP_TO_ORG_ID, L.DELIVER_TO_ORG_ID,
7900: L.VERSION_NUMBER, M1.CUSTOMER_MERGE_ID
7901: FROM
7902: RA_CUSTOMER_MERGES M1, OE_BLANKET_LINES_HIST L , OE_BLANKET_LINES_all LN
7903: WHERE L.INVOICE_TO_ORG_ID= M1.DUPLICATE_SITE_ID
7904: AND M1.PROCESS_FLAG='N'
7905: AND M1.REQUEST_ID = req_id
7906: AND M1.SET_NUMBER = set_num
7909: SELECT L.LINE_ID , L.SOLD_TO_ORG_ID, L.INVOICE_TO_ORG_ID,
7910: L.SHIP_TO_ORG_ID, L.INTMED_SHIP_TO_ORG_ID, L.DELIVER_TO_ORG_ID,
7911: L.VERSION_NUMBER, M1.CUSTOMER_MERGE_ID
7912: FROM
7913: RA_CUSTOMER_MERGES M1, OE_BLANKET_LINES_HIST L , OE_BLANKET_LINES_all LN
7914: WHERE L.SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
7915: AND M1.PROCESS_FLAG='N'
7916: AND M1.REQUEST_ID = req_id
7917: AND M1.SET_NUMBER = set_num
7920: SELECT L.LINE_ID , L.SOLD_TO_ORG_ID, L.INVOICE_TO_ORG_ID,
7921: L.SHIP_TO_ORG_ID, L.INTMED_SHIP_TO_ORG_ID, L.DELIVER_TO_ORG_ID,
7922: L.VERSION_NUMBER, M1.CUSTOMER_MERGE_ID
7923: FROM
7924: RA_CUSTOMER_MERGES M1, OE_BLANKET_LINES_HIST L , OE_BLANKET_LINES_all LN
7925: WHERE L.INTMED_SHIP_TO_ORG_ID= M1.DUPLICATE_SITE_ID
7926: AND M1.PROCESS_FLAG='N'
7927: AND M1.REQUEST_ID = req_id
7928: AND M1.SET_NUMBER = set_num
7931: SELECT L.LINE_ID , L.SOLD_TO_ORG_ID, L.INVOICE_TO_ORG_ID,
7932: L.SHIP_TO_ORG_ID, L.INTMED_SHIP_TO_ORG_ID, L.DELIVER_TO_ORG_ID,
7933: L.VERSION_NUMBER, M1.CUSTOMER_MERGE_ID
7934: FROM
7935: RA_CUSTOMER_MERGES M1, OE_BLANKET_LINES_HIST L , OE_BLANKET_LINES_all LN
7936: WHERE L.DELIVER_TO_ORG_ID= M1.DUPLICATE_SITE_ID
7937: AND M1.PROCESS_FLAG='N'
7938: AND M1.REQUEST_ID = req_id
7939: AND M1.SET_NUMBER = set_num
8204: -- from OE_EM_INFORMATION_ALL
8205: from OE_EM_INFORMATION
8206: where sold_to_org_id in
8207: (select m.duplicate_id
8208: from ra_customer_merges m
8209: where m.process_flag = 'N'
8210: and m.request_id = req_id
8211: and m.set_number = set_num)
8212: for update nowait;
8272: hz_utility_pub.LAST_UPDATE_LOGIN,
8273: hz_utility_pub.LAST_UPDATE_DATE,
8274: hz_utility_pub.LAST_UPDATED_BY
8275: from oe_em_information_All h,
8276: ra_customer_merges m
8277: where h.sold_to_org_id=m.duplicate_site_id
8278: and m.process_flag = 'N'
8279: and m.request_id = req_id
8280: and m.set_number = set_num;
8282: end if;
8283:
8284: UPDATE OE_EM_INFORMATION_ALL a
8285: set sold_to_org_id = (select distinct m.customer_id
8286: from ra_customer_merges m
8287: where a.sold_to_org_id = m.duplicate_id
8288: and m.process_flag = 'N'
8289: and m.request_id = req_id
8290: and m.set_number = set_num),
8295: program_application_id =arp_standard.profile.program_application_id,
8296: program_id = arp_standard.profile.program_id,
8297: program_update_date = sysdate
8298: where sold_to_org_id in (select m.duplicate_id
8299: from ra_customer_merges m
8300: where m.process_flag = 'N'
8301: and m.request_id = req_id
8302: and m.set_number = set_num)
8303: --added for bug3196900