[Home] [Help]
24: AND source_system_name = nvl(p_source_system_name,source_system_name);
25:
26: CURSOR pty1_intf_cur IS
27: SELECT ip_interface_id
28: FROM csi_i_party_interface cpi
29: WHERE cpi.party_source_table = 'HZ_PARTIES'
30: AND cpi.inst_interface_id IN (SELECT inst_interface_id
31: FROM csi_instance_interface cii
32: WHERE cii.transaction_identifier IS NOT NULL
38: AND source_system_name =
39: nvl(p_source_system_name,source_system_name));
40: CURSOR pty2_intf_cur IS
41: SELECT ip_interface_id
42: FROM csi_i_party_interface cpi
43: WHERE cpi.party_source_table = 'EMPLOYEE'
44: AND cpi.inst_interface_id IN (SELECT inst_interface_id
45: FROM csi_instance_interface cii
46: WHERE cii.transaction_identifier IS NOT NULL
52: AND source_system_name =
53: nvl(p_source_system_name,source_system_name));
54: CURSOR pty3_intf_cur IS
55: SELECT ip_interface_id
56: FROM csi_i_party_interface cpi
57: WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
58: FROM csi_instance_interface cii
59: WHERE cii.transaction_identifier IS NOT NULL
60: AND trunc(source_transaction_date)
246: ip_intf_id_upd1
247: LIMIT max_buffer_size;
248:
249: FORALL i1 in 1 .. ip_intf_id_upd1.count
250: UPDATE csi_i_party_interface cpi
251: SET cpi.party_id = (SELECT party_id
252: FROM hz_parties
253: WHERE party_name = cpi.party_name
254: AND party_number = NVL(cpi.party_number,party_number))
256: AND cpi.party_id IS NULL
257: AND cpi.party_name IS NOT NULL;
258:
259: FORALL i2 in 1 .. ip_intf_id_upd1.count
260: UPDATE csi_i_party_interface cpi
261: SET cpi.contact_party_id =
262: (SELECT party_id
263: FROM hz_parties
264: WHERE party_name = cpi.contact_party_name
272: END LOOP;
273: COMMIT;
274: CLOSE pty1_intf_cur;
275:
276: UPDATE CSI_I_PARTY_INTERFACE cpi
277: SET party_id=(SELECT vendor_id
278: FROM po_vendors
279: WHERE vendor_name = cpi.party_name
280: )
298: ip_intf_id_upd2
299: LIMIT max_buffer_size;
300:
301: FORALL i1 in 1 .. ip_intf_id_upd2.count
302: UPDATE csi_i_party_interface cpi
303: SET cpi.party_id = (SELECT person_id
304: FROM per_all_people_f
305: WHERE full_name = cpi.party_name)
306: WHERE ip_interface_id=ip_intf_id_upd2(i1)
307: AND cpi.party_id IS NULL
308: AND cpi.party_name IS NOT NULL;
309:
310: FORALL i2 in 1 .. ip_intf_id_upd2.count
311: UPDATE csi_i_party_interface cpi
312: SET cpi.contact_party_id=
313: (SELECT party_id
314: FROM hz_parties
315: WHERE party_name = cpi.contact_party_name
323: END LOOP;
324: COMMIT;
325: CLOSE pty2_intf_cur;
326:
327: UPDATE CSI_I_PARTY_INTERFACE cpi
328: SET party_id=(SELECT team_id
329: FROM jtf_rs_teams_vl
330: WHERE team_name = cpi.party_name)
331: WHERE cpi.party_source_table = 'TEAM'
339: AND source_system_name = nvl(p_source_system_name,source_system_name))
340: AND cpi.party_id IS NULL
341: AND cpi.party_name IS NOT NULL;
342:
343: UPDATE CSI_I_PARTY_INTERFACE cpi
344: SET party_id = (SELECT group_id
345: FROM jtf_rs_groups_vl
346: WHERE group_name = cpi.party_name)
347: WHERE cpi.party_source_table = 'GROUP'
363: ip_intf_id_upd3
364: LIMIT max_buffer_size;
365:
366: FORALL i1 in 1 .. ip_intf_id_upd3.count
367: UPDATE csi_i_party_interface cpi
368: SET cpi.party_account1_id=
369: (SELECT cust_account_id
370: FROM hz_cust_accounts
371: WHERE account_number = cpi.party_account1_number
375: AND cpi.party_account1_number IS NOT NULL
376: AND cpi.party_id IS NOT NULL;
377:
378: FORALL i2 in 1 .. ip_intf_id_upd3.count
379: UPDATE csi_i_party_interface cpi
380: SET cpi.party_account2_id=
381: (SELECT cust_account_id
382: FROM hz_cust_accounts
383: WHERE account_number = cpi.party_account2_number
387: AND cpi.party_account2_number IS NOT NULL
388: AND cpi.party_id IS NOT NULL;
389:
390: FORALL i3 in 1 .. ip_intf_id_upd3.count
391: UPDATE csi_i_party_interface cpi
392: SET cpi.party_account3_id=
393: (SELECT cust_account_id
394: FROM hz_cust_accounts
395: WHERE account_number = cpi.party_account3_number
551: AND parallel_worker_id = p_worker_id;
552:
553: CURSOR pty1_intf_cur IS
554: SELECT ip_interface_id
555: FROM csi_i_party_interface cpi
556: WHERE cpi.party_source_table = 'HZ_PARTIES'
557: AND cpi.inst_interface_id IN (SELECT inst_interface_Id
558: FROM csi_instance_interface
559: WHERE transaction_identifier IS NULL
566: AND parallel_worker_id = p_worker_id);
567:
568: CURSOR pty2_intf_cur IS
569: SELECT ip_interface_id
570: FROM csi_i_party_interface cpi
571: WHERE cpi.party_source_table = 'EMPLOYEE'
572: AND cpi.inst_interface_id IN (SELECT inst_interface_Id
573: FROM csi_instance_interface
574: WHERE transaction_identifier IS NULL
579: AND source_system_name = nvl(p_source_system_name,source_system_name)
580: AND parallel_worker_id = p_worker_id);
581: CURSOR pty3_intf_cur IS
582: SELECT ip_interface_id
583: FROM csi_i_party_interface cpi
584: WHERE cpi.inst_interface_id IN (SELECT inst_interface_Id
585: FROM csi_instance_interface
586: WHERE transaction_identifier IS NULL
587: AND trunc(source_transaction_date)
773: ip_intf_id_upd1
774: LIMIT max_buffer_size;
775:
776: FORALL i1 in 1 .. ip_intf_id_upd1.count
777: UPDATE csi_i_party_interface cpi
778: SET cpi.party_id = (SELECT party_id
779: FROM hz_parties
780: WHERE party_name = cpi.party_name
781: AND party_number = NVL(cpi.party_number,party_number))
783: AND cpi.party_id IS NULL
784: AND cpi.party_name IS NOT NULL;
785:
786: FORALL i2 in 1 .. ip_intf_id_upd1.count
787: UPDATE csi_i_party_interface cpi
788: SET cpi.contact_party_id =
789: (SELECT party_id
790: FROM hz_parties
791: WHERE party_name = cpi.contact_party_name
798: EXIT WHEN pty1_intf_cur%NOTFOUND;
799: END LOOP;
800: COMMIT;
801: CLOSE pty1_intf_cur;
802: UPDATE CSI_I_PARTY_INTERFACE cpi
803: SET party_id=(SELECT vendor_id
804: FROM po_vendors
805: WHERE vendor_name = cpi.party_name)
806: WHERE cpi.party_source_table = 'PO_VENDORS'
823: ip_intf_id_upd2
824: LIMIT max_buffer_size;
825:
826: FORALL i1 in 1 .. ip_intf_id_upd2.count
827: UPDATE csi_i_party_interface cpi
828: SET cpi.party_id = (SELECT person_id
829: FROM per_all_people_f
830: WHERE full_name = cpi.party_name)
831: WHERE ip_interface_id=ip_intf_id_upd2(i1)
832: AND cpi.party_id IS NULL
833: AND cpi.party_name IS NOT NULL;
834:
835: FORALL i2 in 1 .. ip_intf_id_upd2.count
836: UPDATE csi_i_party_interface cpi
837: SET cpi.contact_party_id=
838: (SELECT party_id
839: FROM hz_parties
840: WHERE party_name = cpi.contact_party_name
848: END LOOP;
849: COMMIT;
850: CLOSE pty2_intf_cur;
851:
852: UPDATE CSI_I_PARTY_INTERFACE cpi
853: SET party_id = (SELECT team_id
854: FROM jtf_rs_teams_vl
855: WHERE team_name = cpi.party_name)
856: WHERE cpi.party_source_table = 'TEAM'
865: AND parallel_worker_id = p_worker_id)
866: AND cpi.party_id IS NULL
867: AND cpi.party_name IS NOT NULL;
868:
869: UPDATE CSI_I_PARTY_INTERFACE cpi
870: SET party_id = (SELECT group_id
871: FROM jtf_rs_groups_vl
872: WHERE group_name = cpi.party_name)
873: WHERE cpi.party_source_table = 'GROUP'
889: ip_intf_id_upd3
890: LIMIT max_buffer_size;
891:
892: FORALL i1 in 1 .. ip_intf_id_upd3.count
893: UPDATE csi_i_party_interface cpi
894: SET cpi.party_account1_id=
895: (SELECT cust_account_id
896: FROM hz_cust_accounts
897: WHERE account_number = cpi.party_account1_number
900: AND cpi.party_account1_id IS NULL
901: AND cpi.party_account1_number IS NOT NULL;
902:
903: FORALL i2 in 1 .. ip_intf_id_upd3.count
904: UPDATE csi_i_party_interface cpi
905: SET cpi.party_account2_id=
906: (SELECT cust_account_id
907: FROM hz_cust_accounts
908: WHERE account_number = cpi.party_account2_number
911: AND cpi.party_account2_id IS NULL
912: AND cpi.party_account2_number IS NOT NULL;
913:
914: FORALL i3 in 1 .. ip_intf_id_upd3.count
915: UPDATE csi_i_party_interface cpi
916: SET cpi.party_account3_id=
917: (SELECT cust_account_id
918: FROM hz_cust_accounts
919: WHERE account_number = cpi.party_account3_number
1057: x_error_message OUT NOCOPY VARCHAR2) IS
1058:
1059: CURSOR pty1_intf_cur IS
1060: SELECT ip_interface_id
1061: FROM csi_i_party_interface cpi
1062: WHERE cpi.party_source_table = 'HZ_PARTIES'
1063: AND cpi.inst_interface_id IN (SELECT inst_interface_id
1064: FROM csi_instance_interface cii
1065: WHERE cii.transaction_identifier = p_txn_identifier)
1066: AND cpi.party_source_table = 'HZ_PARTIES';
1067:
1068: CURSOR pty3_intf_cur IS
1069: SELECT ip_interface_id
1070: FROM csi_i_party_interface cpi
1071: WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
1072: FROM csi_instance_interface cii
1073: WHERE cii.transaction_identifier = p_txn_identifier
1074: AND cii.source_system_name = p_source_system_name);
1124: ip_intf_id_upd1
1125: LIMIT max_buffer_size;
1126:
1127: FORALL i1 in 1 .. ip_intf_id_upd1.count
1128: UPDATE csi_i_party_interface cpi
1129: SET cpi.party_id = (SELECT party_id
1130: FROM hz_parties
1131: WHERE party_name = cpi.party_name
1132: AND party_number = NVL(cpi.party_number,party_number))
1134: AND cpi.party_id IS NULL
1135: AND cpi.party_name IS NOT NULL;
1136:
1137: FORALL i2 in 1 .. ip_intf_id_upd1.count
1138: UPDATE csi_i_party_interface cpi
1139: SET cpi.contact_party_id =
1140: (SELECT party_id
1141: FROM hz_parties
1142: WHERE party_name = cpi.contact_party_name
1150: END LOOP;
1151: COMMIT;
1152: CLOSE pty1_intf_cur;
1153:
1154: UPDATE CSI_I_PARTY_INTERFACE cpi
1155: SET party_id = (SELECT vendor_id
1156: FROM po_vendors
1157: WHERE vendor_name = cpi.party_name)
1158: WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
1161: AND cpi.party_source_table = 'PO_VENDORS'
1162: AND cpi.party_id IS NULL
1163: AND cpi.party_name IS NOT NULL;
1164:
1165: UPDATE CSI_I_PARTY_INTERFACE cpi
1166: SET party_id = (SELECT person_id
1167: FROM per_all_people_f
1168: WHERE full_name = cpi.party_name)
1169: WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
1172: AND cpi.party_source_table = 'EMPLOYEE'
1173: AND cpi.party_id IS NULL
1174: AND cpi.party_name IS NOT NULL;
1175:
1176: UPDATE CSI_I_PARTY_INTERFACE cpi
1177: SET party_id = (SELECT team_id
1178: FROM jtf_rs_teams_vl
1179: WHERE team_name = cpi.party_name)
1180: WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
1183: AND cpi.party_source_table = 'TEAM'
1184: AND cpi.party_id IS NULL
1185: AND cpi.party_name IS NOT NULL;
1186:
1187: UPDATE CSI_I_PARTY_INTERFACE cpi
1188: SET party_id = (SELECT group_id
1189: FROM jtf_rs_groups_vl
1190: WHERE group_name = cpi.party_name)
1191: WHERE cpi.inst_interface_id IN (SELECT inst_interface_id
1201: ip_intf_id_upd3
1202: LIMIT max_buffer_size;
1203:
1204: FORALL i1 in 1 .. ip_intf_id_upd3.count
1205: UPDATE csi_i_party_interface cpi
1206: SET cpi.party_account1_id=
1207: (SELECT cust_account_id
1208: FROM hz_cust_accounts
1209: WHERE account_number = cpi.party_account1_number
1213: AND cpi.party_account1_number IS NOT NULL
1214: AND cpi.party_id IS NOT NULL;
1215:
1216: FORALL i2 in 1 .. ip_intf_id_upd3.count
1217: UPDATE csi_i_party_interface cpi
1218: SET cpi.party_account2_id=
1219: (SELECT cust_account_id
1220: FROM hz_cust_accounts
1221: WHERE account_number = cpi.party_account2_number
1225: AND cpi.party_account2_number IS NOT NULL
1226: AND cpi.party_id IS NOT NULL;
1227:
1228: FORALL i3 in 1 .. ip_intf_id_upd3.count
1229: UPDATE csi_i_party_interface cpi
1230: SET cpi.party_account3_id=
1231: (SELECT cust_account_id
1232: FROM hz_cust_accounts
1233: WHERE account_number = cpi.party_account3_number
1242: END LOOP;
1243: COMMIT;
1244: CLOSE pty3_intf_cur;
1245:
1246: UPDATE csi_i_party_interface a
1247: SET instance_party_id = (SELECT instance_party_id
1248: FROM csi_i_parties
1249: WHERE party_id = a.party_id
1250: AND instance_id =
1260: AND cii.source_system_name = p_source_system_name)
1261: AND instance_party_id IS NULL
1262: AND a.party_id IS NOT NULL;
1263:
1264: UPDATE csi_i_party_interface a
1265: SET contact_ip_id = (SELECT instance_party_id
1266: FROM csi_i_parties
1267: WHERE party_id = a.contact_party_id
1268: AND instance_id =
1278: AND cii.source_system_name = p_source_system_name)
1279: AND contact_ip_id IS NULL
1280: AND a.contact_party_id IS NOT NULL;
1281:
1282: UPDATE csi_i_party_interface a
1283: SET instance_party_id = (SELECT instance_party_id
1284: FROM csi_i_parties
1285: WHERE instance_id =
1286: (SELECT instance_id
1294: AND cii.source_system_name = p_source_system_name)
1295: AND party_relationship_type_code = 'OWNER'
1296: AND instance_party_id IS NULL;
1297:
1298: UPDATE csi_i_party_interface a
1299: SET ip_account1_id=(SELECT ip_account_id
1300: FROM csi_ip_accounts
1301: WHERE instance_party_id = a.instance_party_id
1302: AND party_account_id = a.party_account1_id
1307: WHERE cii.transaction_identifier = p_txn_identifier
1308: AND cii.source_system_name = p_source_system_name)
1309: AND ip_account1_id IS NULL ;
1310:
1311: UPDATE csi_i_party_interface a
1312: SET ip_account2_id = (SELECT ip_account_id
1313: FROM csi_ip_accounts
1314: WHERE instance_party_id = a.instance_party_id
1315: AND party_account_id = a.party_account2_id
1320: WHERE cii.transaction_identifier = p_txn_identifier
1321: AND cii.source_system_name = p_source_system_name)
1322: AND ip_account2_id IS NULL;
1323:
1324: UPDATE csi_i_party_interface a
1325: SET ip_account3_id = (SELECT ip_account_id
1326: FROM csi_ip_accounts
1327: WHERE instance_party_id = a.instance_party_id
1328: AND party_account_id = a.party_account3_id
1333: WHERE cii.transaction_identifier = p_txn_identifier
1334: AND cii.source_system_name = p_source_system_name)
1335: AND ip_account3_id IS NULL;
1336:
1337: UPDATE csi_i_party_interface a
1338: SET ip_account1_id = (SELECT ip_account_id
1339: FROM csi_ip_accounts
1340: WHERE instance_party_id = a.instance_party_id
1341: AND relationship_type_code =
1346: AND cii.source_system_name = p_source_system_name)
1347: AND acct1_relationship_type_code = 'OWNER'
1348: AND ip_account1_id IS NULL;
1349:
1350: UPDATE csi_i_party_interface a
1351: SET ip_account2_id = (SELECT ip_account_id
1352: FROM csi_ip_accounts
1353: WHERE instance_party_id = a.instance_party_id
1354: AND relationship_type_code =
1359: AND cii.source_system_name = p_source_system_name)
1360: AND acct2_relationship_type_code = 'OWNER'
1361: AND ip_account2_id IS NULL;
1362:
1363: UPDATE csi_i_party_interface a
1364: SET ip_account3_id = (SELECT ip_account_id
1365: FROM csi_ip_accounts
1366: WHERE instance_party_id = a.instance_party_id
1367: AND relationship_type_code =