[Home] [Help]
13: l_txn_to_date DATE := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
14:
15: CURSOR ins_intf_cur IS
16: SELECT inst_interface_id
17: FROM csi_instance_interface
18: WHERE trunc(source_transaction_date)
19: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
20: AND nvl(l_txn_to_date,trunc(source_transaction_date))
21: AND process_status IN ('X','R')
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
33: AND trunc(source_transaction_date)
34: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
35: AND nvl(l_txn_to_date,trunc(source_transaction_date))
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
47: AND trunc(source_transaction_date)
48: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
49: AND nvl(l_txn_to_date,trunc(source_transaction_date))
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)
61: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
62: AND nvl(l_txn_to_date,trunc(source_transaction_date))
68: SELECT ieav_interface_id
69: FROM csi_iea_value_interface a
70: WHERE a.attribute_level = 'ITEM'
71: AND a.inst_interface_id IN (SELECT inst_interface_id
72: FROM csi_instance_interface cii
73: WHERE cii.transaction_identifier IS NOT NULL
74: AND trunc(source_transaction_date)
75: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
76: AND nvl(l_txn_to_date,trunc(source_transaction_date))
82: CURSOR asst_intf_cur IS
83: SELECT ia_interface_id
84: FROM csi_i_asset_interface a
85: WHERE a.inst_interface_id IN (SELECT inst_interface_id
86: FROM csi_instance_interface cii
87: WHERE cii.transaction_identifier IS NOT NULL
88: AND trunc(source_transaction_date)
89: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
90: AND nvl(l_txn_to_date,trunc(source_transaction_date))
121: inst_intf_id_upd
122: LIMIT max_buffer_size;
123:
124: FORALL i1 in 1 .. inst_intf_id_upd.count
125: UPDATE csi_instance_interface a
126: SET a.inventory_item_id =
127: (SELECT inventory_item_id
128: FROM mtl_system_items_kfv
129: WHERE concatenated_segments =
133: AND a.inventory_item_id IS NULL
134: AND a.inv_concatenated_segments IS NOT NULL;
135:
136: FORALL i2 in 1 .. inst_intf_id_upd.count
137: UPDATE csi_instance_interface a
138: SET a.inv_vld_organization_id =
139: (SELECT organization_id
140: FROM hr_all_organization_units
141: WHERE name = a.inv_vld_organization_name)
143: AND a.inv_vld_organization_id IS NULL
144: AND a.inv_vld_organization_name IS NOT NULL;
145:
146: FORALL i3 in 1 .. inst_intf_id_upd.count
147: UPDATE csi_instance_interface a
148: SET a.instance_condition_id =
149: (SELECT status_id
150: FROM mtl_material_statuses
151: WHERE status_code = a.instance_condition)
153: AND a.instance_condition_id IS NULL
154: AND a.instance_condition IS NOT NULL;
155:
156: FORALL i4 in 1 .. inst_intf_id_upd.count
157: UPDATE csi_instance_interface a
158: SET a.instance_status_id =
159: (SELECT instance_status_id
160: FROM csi_instance_statuses
161: WHERE name = a.instance_status)
164: AND a.instance_status IS NOT NULL;
165:
166:
167: FORALL i6 in 1 .. inst_intf_id_upd.count
168: UPDATE csi_instance_interface a
169: SET a.system_id = (SELECT system_id
170: FROM csi_systems_b
171: WHERE system_number = a.system_number)
172: WHERE inst_interface_id=inst_intf_id_upd(i6)
173: AND a.system_id IS NULL
174: AND a.system_number IS NOT NULL;
175:
176: FORALL i7 in 1 .. inst_intf_id_upd.count
177: UPDATE csi_instance_interface a
178: SET a.unit_of_measure_code =
179: (SELECT uom_code
180: FROM mtl_units_of_measure_vl
181: WHERE unit_of_measure_tl = a.unit_of_measure)
183: AND a.unit_of_measure_code IS NULL
184: AND a.unit_of_measure IS NOT NULL;
185:
186: FORALL i8 in 1 .. inst_intf_id_upd.count
187: UPDATE csi_instance_interface a
188: SET a.inv_organization_id =
189: (SELECT organization_id
190: FROM hr_all_organization_units
191: WHERE NAME = a.inv_organization_name)
193: AND a.inv_organization_id IS NULL
194: AND a.inv_organization_name IS NOT NULL;
195:
196: FORALL i9 in 1 .. inst_intf_id_upd.count
197: UPDATE csi_instance_interface a
198: SET a.project_id = (SELECT project_id
199: FROM pa_projects_all
200: WHERE segment1 = a.project_number)
201: WHERE inst_interface_id=inst_intf_id_upd(i9)
202: AND a.project_id IS NULL
203: AND a.project_number IS NOT NULL;
204:
205: FORALL i10 in 1 .. inst_intf_id_upd.count
206: UPDATE csi_instance_interface a
207: SET a.task_id = (SELECT task_id
208: FROM pa_tasks pt,
209: pa_projects_all pp
210: WHERE pt.task_number = a.task_number
215: AND a.task_number IS NOT NULL
216: AND a.project_number IS NOT NULL;
217:
218: FORALL i11 in 1 .. inst_intf_id_upd.count
219: UPDATE csi_instance_interface a
220: SET a.wip_job_id = (SELECT wip_entity_id
221: FROM wip_entities
222: WHERE wip_entity_name = a.wip_job_name)
223: WHERE inst_interface_id=inst_intf_id_upd(i11)
225: AND a.wip_job_name IS NOT NULL;
226:
227:
228: FORALL i16 in 1 .. inst_intf_id_upd.count
229: UPDATE csi_instance_interface a
230: SET a.operating_unit=(SELECT organization_id
231: FROM hr_operating_units
232: WHERE name = a.operating_unit_name)
233: WHERE inst_interface_id=inst_intf_id_upd(i16)
279: WHERE vendor_name = cpi.party_name
280: )
281: WHERE cpi.party_source_table = 'PO_VENDORS'
282: AND cpi.inst_interface_id IN (SELECT inst_interface_id
283: FROM csi_instance_interface cii
284: WHERE cii.transaction_identifier IS NOT NULL
285: AND trunc(source_transaction_date)
286: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
287: AND nvl(l_txn_to_date,trunc(source_transaction_date))
329: FROM jtf_rs_teams_vl
330: WHERE team_name = cpi.party_name)
331: WHERE cpi.party_source_table = 'TEAM'
332: AND cpi.inst_interface_id IN (SELECT inst_interface_id
333: FROM csi_instance_interface cii
334: WHERE cii.transaction_identifier IS NOT NULL
335: AND trunc(source_transaction_date)
336: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
337: AND nvl(l_txn_to_date,trunc(source_transaction_date))
345: FROM jtf_rs_groups_vl
346: WHERE group_name = cpi.party_name)
347: WHERE cpi.party_source_table = 'GROUP'
348: AND cpi.inst_interface_id IN (SELECT inst_interface_id
349: FROM csi_instance_interface cii
350: WHERE cii.transaction_identifier IS NOT NULL
351: AND trunc(source_transaction_date)
352: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
353: AND nvl(l_txn_to_date,trunc(source_transaction_date))
470: WHERE attribute_level = a.attribute_level
471: AND attribute_code = a.attribute_code)
472: WHERE a.attribute_level = 'GLOBAL'
473: AND a.inst_interface_id IN (SELECT inst_interface_id
474: FROM csi_instance_interface cii
475: WHERE cii.transaction_identifier IS NOT NULL
476: AND trunc(source_transaction_date)
477: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
478: AND nvl(l_txn_to_date,trunc(source_transaction_date))
494: AND NVL(attribute_category,'$CSI_NULL_VALUE$')=
495: NVL(a.attribute_category,'$CSI_NULL_VALUE$'))
496: WHERE a.attribute_level = 'ITEM'
497: AND a.inst_interface_id IN (SELECT inst_interface_id
498: FROM csi_instance_interface cii
499: WHERE cii.transaction_identifier IS NOT NULL
500: AND trunc(source_transaction_date)
501: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
502: AND nvl(l_txn_to_date,trunc(source_transaction_date))
540: l_txn_to_date DATE := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
541:
542: CURSOR ins_intf_cur IS
543: SELECT inst_interface_id
544: FROM csi_instance_interface
545: WHERE trunc(source_transaction_date)
546: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
547: AND nvl(l_txn_to_date,trunc(source_transaction_date))
548: AND nvl(transaction_identifier,'-1') = '-1'
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
560: AND trunc(source_transaction_date)
561: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
562: AND nvl(l_txn_to_date,trunc(source_transaction_date))
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
575: AND trunc(source_transaction_date)
576: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
577: AND nvl(l_txn_to_date,trunc(source_transaction_date))
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)
588: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
589: AND nvl(l_txn_to_date,trunc(source_transaction_date))
595: SELECT ieav_interface_id
596: FROM csi_iea_value_interface a
597: WHERE a.attribute_level = 'ITEM'
598: AND a.inst_interface_id IN (SELECT inst_interface_Id
599: FROM csi_instance_interface
600: WHERE transaction_identifier IS NULL
601: AND trunc(source_transaction_date)
602: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
603: AND nvl(l_txn_to_date,trunc(source_transaction_date))
608: CURSOR asst_intf_cur IS
609: SELECT ia_interface_id
610: FROM csi_i_asset_interface a
611: WHERE a.inst_interface_id IN (SELECT inst_interface_Id
612: FROM csi_instance_interface
613: WHERE transaction_identifier IS NULL
614: AND trunc(source_transaction_date)
615: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
616: AND nvl(l_txn_to_date,trunc(source_transaction_date))
650: inst_intf_id_upd
651: LIMIT max_buffer_size;
652:
653: FORALL i1 in 1 .. inst_intf_id_upd.count
654: UPDATE csi_instance_interface a
655: SET a.inventory_item_id =
656: (SELECT inventory_item_id
657: FROM mtl_system_items_kfv
658: WHERE concatenated_segments =
662: AND a.inventory_item_id IS NULL
663: AND a.inv_concatenated_segments IS NOT NULL;
664:
665: FORALL i2 in 1 .. inst_intf_id_upd.count
666: UPDATE csi_instance_interface a
667: SET a.inv_vld_organization_id =
668: (SELECT organization_id
669: FROM hr_all_organization_units
670: WHERE name = a.inv_vld_organization_name)
672: AND a.inv_vld_organization_id IS NULL
673: AND a.inv_vld_organization_name IS NOT NULL;
674:
675: FORALL i3 in 1 .. inst_intf_id_upd.count
676: UPDATE csi_instance_interface a
677: SET a.instance_condition_id =
678: (SELECT status_id
679: FROM mtl_material_statuses
680: WHERE status_code = a.instance_condition)
682: AND a.instance_condition_id IS NULL
683: AND a.instance_condition IS NOT NULL;
684:
685: FORALL i4 in 1 .. inst_intf_id_upd.count
686: UPDATE csi_instance_interface a
687: SET a.instance_status_id =
688: (SELECT instance_status_id
689: FROM csi_instance_statuses
690: WHERE name = a.instance_status)
692: AND a.instance_status_id IS NULL
693: AND a.instance_status IS NOT NULL;
694:
695: FORALL i6 in 1 .. inst_intf_id_upd.count
696: UPDATE csi_instance_interface a
697: SET a.system_id = (SELECT system_id
698: FROM csi_systems_b
699: WHERE system_number = a.system_number)
700: WHERE inst_interface_id=inst_intf_id_upd(i6)
701: AND a.system_id IS NULL
702: AND a.system_number IS NOT NULL;
703:
704: FORALL i7 in 1 .. inst_intf_id_upd.count
705: UPDATE csi_instance_interface a
706: SET a.unit_of_measure_code =
707: (SELECT uom_code
708: FROM mtl_units_of_measure_vl
709: WHERE unit_of_measure_tl = a.unit_of_measure)
711: AND a.unit_of_measure_code IS NULL
712: AND a.unit_of_measure IS NOT NULL;
713:
714: FORALL i8 in 1 .. inst_intf_id_upd.count
715: UPDATE csi_instance_interface a
716: SET a.inv_organization_id =
717: (SELECT organization_id
718: FROM hr_all_organization_units
719: WHERE NAME = a.inv_organization_name)
721: AND a.inv_organization_id IS NULL
722: AND a.inv_organization_name IS NOT NULL;
723:
724: FORALL i9 in 1 .. inst_intf_id_upd.count
725: UPDATE csi_instance_interface a
726: SET a.project_id = (SELECT project_id
727: FROM pa_projects_all
728: WHERE segment1 = a.project_number)
729: WHERE inst_interface_id=inst_intf_id_upd(i9)
730: AND a.project_id IS NULL
731: AND a.project_number IS NOT NULL;
732:
733: FORALL i10 in 1 .. inst_intf_id_upd.count
734: UPDATE csi_instance_interface a
735: SET a.task_id = (SELECT task_id
736: FROM pa_tasks pt,
737: pa_projects_all pp
738: WHERE pt.task_number = a.task_number
743: AND a.task_number IS NOT NULL
744: AND a.project_number IS NOT NULL;
745:
746: FORALL i11 in 1 .. inst_intf_id_upd.count
747: UPDATE csi_instance_interface a
748: SET a.wip_job_id = (SELECT wip_entity_id
749: FROM wip_entities
750: WHERE wip_entity_name = a.wip_job_name)
751: WHERE inst_interface_id=inst_intf_id_upd(i11)
752: AND a.wip_job_id IS NULL
753: AND a.wip_job_name IS NOT NULL;
754:
755: FORALL i16 in 1 .. inst_intf_id_upd.count
756: UPDATE csi_instance_interface a
757: SET a.operating_unit=(SELECT organization_id
758: FROM hr_operating_units
759: WHERE name = a.operating_unit_name)
760: WHERE inst_interface_id=inst_intf_id_upd(i16)
804: FROM po_vendors
805: WHERE vendor_name = cpi.party_name)
806: WHERE cpi.party_source_table = 'PO_VENDORS'
807: AND cpi.inst_interface_id IN (SELECT inst_interface_Id
808: FROM csi_instance_interface
809: WHERE transaction_identifier IS NULL
810: AND trunc(source_transaction_date)
811: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
812: AND nvl(l_txn_to_date,trunc(source_transaction_date))
854: FROM jtf_rs_teams_vl
855: WHERE team_name = cpi.party_name)
856: WHERE cpi.party_source_table = 'TEAM'
857: AND cpi.inst_interface_id IN (SELECT inst_interface_Id
858: FROM csi_instance_interface
859: WHERE transaction_identifier IS NULL
860: AND trunc(source_transaction_date)
861: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
862: AND nvl(l_txn_to_date,trunc(source_transaction_date))
871: FROM jtf_rs_groups_vl
872: WHERE group_name = cpi.party_name)
873: WHERE cpi.party_source_table = 'GROUP'
874: AND cpi.inst_interface_id IN (SELECT inst_interface_Id
875: FROM csi_instance_interface
876: WHERE transaction_identifier IS NULL
877: AND trunc(source_transaction_date)
878: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
879: AND nvl(l_txn_to_date,trunc(source_transaction_date))
993: WHERE attribute_level = a.attribute_level
994: AND attribute_code = a.attribute_code)
995: WHERE a.attribute_level = 'GLOBAL'
996: AND a.inst_interface_id IN (SELECT inst_interface_Id
997: FROM csi_instance_interface
998: WHERE transaction_identifier IS NULL
999: AND trunc(source_transaction_date)
1000: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
1001: AND nvl(l_txn_to_date,trunc(source_transaction_date))
1016: AND NVL(attribute_category,l_fnd_g_char)=
1017: NVL(a.attribute_category,l_fnd_g_char) )
1018: WHERE a.attribute_level = 'ITEM'
1019: AND a.inst_interface_id IN (SELECT inst_interface_Id
1020: FROM csi_instance_interface
1021: WHERE transaction_identifier IS NULL
1022: AND trunc(source_transaction_date)
1023: BETWEEN nvl(l_txn_from_date,trunc(source_transaction_date))
1024: AND nvl(l_txn_to_date,trunc(source_transaction_date))
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
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);
1075:
1076: TYPE NumTabType IS VARRAY(10000) OF NUMBER;
1092: BEGIN
1093:
1094: x_return_status := l_fnd_success;
1095:
1096: UPDATE csi_instance_interface a
1097: SET pricing_attribute_id =
1098: (SELECT pricing_attribute_id
1099: FROM csi_i_pricing_attribs
1100: WHERE instance_id = a.instance_id
1104: AND a.pricing_attribute_id IS NULL
1105: AND a.instance_id IS NOT NULL
1106: AND a.pricing_context IS NOT NULL;
1107:
1108: UPDATE csi_instance_interface a
1109: SET instance_ou_id = (SELECT instance_ou_id
1110: FROM csi_i_org_assignments
1111: WHERE instance_id = a.instance_id
1112: AND operating_unit_id = a.operating_unit
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
1159: FROM csi_instance_interface cii
1160: WHERE cii.transaction_identifier = p_txn_identifier)
1161: AND cpi.party_source_table = 'PO_VENDORS'
1162: AND cpi.party_id IS NULL
1163: AND cpi.party_name IS NOT NULL;
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
1170: FROM csi_instance_interface cii
1171: WHERE cii.transaction_identifier = p_txn_identifier)
1172: AND cpi.party_source_table = 'EMPLOYEE'
1173: AND cpi.party_id IS NULL
1174: AND cpi.party_name IS NOT NULL;
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
1181: FROM csi_instance_interface cii
1182: WHERE cii.transaction_identifier = p_txn_identifier)
1183: AND cpi.party_source_table = 'TEAM'
1184: AND cpi.party_id IS NULL
1185: AND cpi.party_name IS NOT NULL;
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
1192: FROM csi_instance_interface cii
1193: WHERE cii.transaction_identifier = p_txn_identifier)
1194: AND cpi.party_source_table = 'GROUP'
1195: AND cpi.party_id IS NULL
1196: AND cpi.party_name IS NOT NULL;
1248: FROM csi_i_parties
1249: WHERE party_id = a.party_id
1250: AND instance_id =
1251: (SELECT instance_id
1252: FROM csi_instance_interface cii
1253: WHERE cii.inst_interface_id=a.inst_interface_id)
1254: AND relationship_type_code =
1255: a.party_relationship_type_code
1256: AND contact_flag <>'Y')
1254: AND relationship_type_code =
1255: a.party_relationship_type_code
1256: AND contact_flag <>'Y')
1257: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1258: FROM csi_instance_interface cii
1259: WHERE cii.transaction_identifier = p_txn_identifier
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;
1266: FROM csi_i_parties
1267: WHERE party_id = a.contact_party_id
1268: AND instance_id =
1269: (SELECT instance_id
1270: FROM csi_instance_interface cii
1271: WHERE cii.inst_interface_id=a.inst_interface_id)
1272: AND relationship_type_code =
1273: a.contact_party_rel_type
1274: AND contact_flag <>'Y')
1272: AND relationship_type_code =
1273: a.contact_party_rel_type
1274: AND contact_flag <>'Y')
1275: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1276: FROM csi_instance_interface cii
1277: WHERE cii.transaction_identifier = p_txn_identifier
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;
1283: SET instance_party_id = (SELECT instance_party_id
1284: FROM csi_i_parties
1285: WHERE instance_id =
1286: (SELECT instance_id
1287: FROM csi_instance_interface cii
1288: WHERE cii.inst_interface_id=a.inst_interface_id)
1289: AND relationship_type_code =
1290: a.party_relationship_type_code)
1291: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1288: WHERE cii.inst_interface_id=a.inst_interface_id)
1289: AND relationship_type_code =
1290: a.party_relationship_type_code)
1291: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1292: FROM csi_instance_interface cii
1293: WHERE cii.transaction_identifier = p_txn_identifier
1294: AND cii.source_system_name = p_source_system_name)
1295: AND party_relationship_type_code = 'OWNER'
1296: AND instance_party_id IS NULL;
1302: AND party_account_id = a.party_account1_id
1303: AND relationship_type_code =
1304: a.acct1_relationship_type_code)
1305: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1306: FROM csi_instance_interface cii
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:
1315: AND party_account_id = a.party_account2_id
1316: AND relationship_type_code =
1317: a.acct2_relationship_type_code)
1318: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1319: FROM csi_instance_interface cii
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:
1328: AND party_account_id = a.party_account3_id
1329: AND relationship_type_code =
1330: a.acct3_relationship_type_code)
1331: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1332: FROM csi_instance_interface cii
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:
1340: WHERE instance_party_id = a.instance_party_id
1341: AND relationship_type_code =
1342: a.acct1_relationship_type_code)
1343: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1344: FROM csi_instance_interface cii
1345: WHERE cii.transaction_identifier = p_txn_identifier
1346: AND cii.source_system_name = p_source_system_name)
1347: AND acct1_relationship_type_code = 'OWNER'
1348: AND ip_account1_id IS NULL;
1353: WHERE instance_party_id = a.instance_party_id
1354: AND relationship_type_code =
1355: a.acct2_relationship_type_code)
1356: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1357: FROM csi_instance_interface cii
1358: WHERE cii.transaction_identifier = p_txn_identifier
1359: AND cii.source_system_name = p_source_system_name)
1360: AND acct2_relationship_type_code = 'OWNER'
1361: AND ip_account2_id IS NULL;
1366: WHERE instance_party_id = a.instance_party_id
1367: AND relationship_type_code =
1368: a.acct3_relationship_type_code)
1369: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1370: FROM csi_instance_interface cii
1371: WHERE cii.transaction_identifier = p_txn_identifier
1372: AND cii.source_system_name = p_source_system_name)
1373: AND acct3_relationship_type_code = 'OWNER'
1374: AND ip_account3_id IS NULL;
1378: FROM csi_i_extended_attribs
1379: WHERE attribute_level = a.attribute_level
1380: AND attribute_code = a.attribute_code)
1381: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1382: FROM csi_instance_interface cii
1383: WHERE cii.transaction_identifier = p_txn_identifier
1384: AND cii.source_system_name = p_source_system_name)
1385: AND attribute_level = 'GLOBAL'
1386: AND attribute_id IS NULL;
1394: AND master_organization_id = a.master_organization_id
1395: AND NVL(attribute_category,l_fnd_g_char)=
1396: NVL(a.attribute_category,l_fnd_g_char))
1397: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1398: FROM csi_instance_interface cii
1399: WHERE cii.transaction_identifier = p_txn_identifier
1400: AND cii.source_system_name = p_source_system_name)
1401: AND attribute_level = 'ITEM'
1402: AND attribute_id IS NULL;
1407: WHERE attribute_id = a.attribute_id
1408: AND attribute_value = a.attribute_value
1409: AND instance_id =
1410: (SELECT cii1.instance_id
1411: FROM csi_instance_interface cii1
1412: WHERE cii1.inst_interface_id =a.inst_interface_id))
1413: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1414: FROM csi_instance_interface cii
1415: WHERE cii.transaction_identifier = p_txn_identifier
1410: (SELECT cii1.instance_id
1411: FROM csi_instance_interface cii1
1412: WHERE cii1.inst_interface_id =a.inst_interface_id))
1413: WHERE a.inst_interface_id IN (SELECT inst_interface_id
1414: FROM csi_instance_interface cii
1415: WHERE cii.transaction_identifier = p_txn_identifier
1416: AND cii.source_system_name = p_source_system_name)
1417: AND attribute_value_id IS NULL;
1418:
1453: l_txn_from_date DATE := to_date(p_txn_from_date, 'YYYY/MM/DD HH24:MI:SS');
1454: l_txn_to_date DATE := to_date(p_txn_to_date, 'YYYY/MM/DD HH24:MI:SS');
1455:
1456: CURSOR c_error is
1457: SELECT * from csi_instance_interface
1458: WHERE process_status = 'E'
1459: AND trunc(source_transaction_date) BETWEEN
1460: nvl(l_txn_from_date,trunc(source_transaction_date)) AND
1461: nvl(l_txn_to_date,trunc(source_transaction_date))
1504: CURSOR c_error (pc_txn_from_date IN DATE,
1505: pc_txn_to_date IN DATE,
1506: pc_source_system_name IN VARCHAR2,
1507: pc_worker_id IN NUMBER) is
1508: SELECT * from csi_instance_interface
1509: WHERE process_status = 'E'
1510: AND trunc(source_transaction_date) BETWEEN
1511: nvl(pc_txn_from_date,trunc(source_transaction_date)) AND
1512: nvl(pc_txn_to_date,trunc(source_transaction_date))
1639: --Changed for bug 5875269
1640: UPDATE csi_ii_relation_interface cir
1641: SET cir.object_id = decode(cir.object_id,NULL
1642: ,(SELECT cii.instance_id
1643: FROM csi_instance_interface cii
1644: WHERE cii.inst_interface_id = cir.object_interface_id)
1645: ,cir.object_id ),
1646: cir.subject_id = decode(cir.subject_id,NULL
1647: ,(SELECT cii.instance_id
1644: WHERE cii.inst_interface_id = cir.object_interface_id)
1645: ,cir.object_id ),
1646: cir.subject_id = decode(cir.subject_id,NULL
1647: ,(SELECT cii.instance_id
1648: FROM csi_instance_interface cii
1649: WHERE cii.inst_interface_id = cir.subject_interface_id)
1650: ,cir.subject_id )
1651: WHERE ((cir.object_id IS NULL AND EXISTS (SELECT 'x' FROM csi_instance_interface WHERE
1652: inst_interface_id = cir.object_interface_id AND instance_id IS NOT NULL))
1647: ,(SELECT cii.instance_id
1648: FROM csi_instance_interface cii
1649: WHERE cii.inst_interface_id = cir.subject_interface_id)
1650: ,cir.subject_id )
1651: WHERE ((cir.object_id IS NULL AND EXISTS (SELECT 'x' FROM csi_instance_interface WHERE
1652: inst_interface_id = cir.object_interface_id AND instance_id IS NOT NULL))
1653: OR (cir.subject_id IS NULL AND EXISTS (SELECT 'x' FROM csi_instance_interface WHERE
1654: inst_interface_id = cir.subject_interface_id AND instance_id IS NOT NULL)))
1655: AND cir.process_status='R'
1649: WHERE cii.inst_interface_id = cir.subject_interface_id)
1650: ,cir.subject_id )
1651: WHERE ((cir.object_id IS NULL AND EXISTS (SELECT 'x' FROM csi_instance_interface WHERE
1652: inst_interface_id = cir.object_interface_id AND instance_id IS NOT NULL))
1653: OR (cir.subject_id IS NULL AND EXISTS (SELECT 'x' FROM csi_instance_interface WHERE
1654: inst_interface_id = cir.subject_interface_id AND instance_id IS NOT NULL)))
1655: AND cir.process_status='R'
1656: AND ROWNUM<10001;
1657: