[Home] [Help]
39: SELECT distinct CUSTOMER_MERGE_HEADER_ID
40: ,cash_receipt_id
41: ,pay_from_customer
42: ,customer_site_use_id
43: FROM AR_CASH_RECEIPTS yt, ra_customer_merges m
44: WHERE ( (yt.pay_from_customer = m.DUPLICATE_ID AND
45: nvl(yt.customer_site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID))
46: AND m.process_flag = 'N'
47: AND m.request_id = req_id
170: SELECT distinct CUSTOMER_MERGE_HEADER_ID
171: ,payment_schedule_id
172: ,yt.customer_id
173: ,customer_site_use_id
174: FROM AR_PAYMENT_SCHEDULES yt, ra_customer_merges m
175: WHERE ( yt.customer_id = m.DUPLICATE_ID AND
176: nvl(yt.customer_site_use_id, m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID)
177: AND m.process_flag = 'N'
178: AND m.request_id = req_id
359: ,sold_to_customer_id
360: ,sold_to_site_use_id
361: ,DRAWEE_ID
362: ,DRAWEE_SITE_USE_ID
363: FROM RA_CUSTOMER_TRX yt, ra_customer_merges m
364: WHERE ( (yt.bill_to_customer_id = m.DUPLICATE_ID AND
365: yt.bill_to_site_use_id = m.DUPLICATE_SITE_ID)
366: OR (yt.paying_customer_id = m.DUPLICATE_ID AND
367: yt.paying_site_use_id = m.DUPLICATE_SITE_ID)
557: ,customer_trx_line_id
558: ,ship_to_customer_id
559: ,ship_to_site_use_id
560:
561: FROM RA_CUSTOMER_TRX_LINES yt , ra_customer_merges m
562: WHERE
563: yt.ship_to_customer_id = m.DUPLICATE_ID AND
564: nvl(yt.ship_to_site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID
565:
696: SELECT distinct CUSTOMER_MERGE_HEADER_ID
697: ,receivable_application_id
698: ,on_acct_cust_id
699: ,on_acct_cust_site_use_id
700: FROM ar_receivable_applications yt, ra_customer_merges m
701: WHERE (yt.on_acct_cust_id = m.DUPLICATE_ID AND
702: nvl(yt.on_acct_cust_site_use_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID)
703: AND m.process_flag = 'N'
704: AND m.request_id = req_id
874:
875: /* Bug3500125 : Added following table type and added CUSTOMER_REF to CURSOR merged_records*/
876:
877: TYPE CUSTOMER_REF_LIST_TYPE IS TABLE OF
878: RA_CUSTOMER_MERGES.CUSTOMER_REF%TYPE
879: INDEX BY BINARY_INTEGER;
880: VCHAR_COL6_ORIG_LIST CUSTOMER_REF_LIST_TYPE;
881: VCHAR_COL6_NEW_LIST CUSTOMER_REF_LIST_TYPE;
882:
881: VCHAR_COL6_NEW_LIST CUSTOMER_REF_LIST_TYPE;
882:
883: /*Additional change for ra_interface_lines under bug2447449*/
884: /* bug3667197: Modified the where clause of cursor merged_records to avoid
885: FTS on table ra_customer_merges */
886: /* bug4075234: Replaced 'exists' clause in CURSOR merged_records with 'IN' for performance
887: improvement */
888: l_profile_val VARCHAR2(30);
889:
903: ,ORIG_SYSTEM_BILL_ADDRESS_REF
904: ,ORIG_SYSTEM_SHIP_ADDRESS_REF
905: ,m.CUSTOMER_REF
906: from ra_interface_lines rai,
907: ra_customer_merges m
908: where nvl(rai.interface_status,'N') <> 'P'
909: and m.process_flag = 'N'
910: and m.request_id = req_id
911: and m.set_number = set_num
924: ,ORIG_SYSTEM_BILL_ADDRESS_REF
925: ,ORIG_SYSTEM_SHIP_ADDRESS_REF
926: ,m.CUSTOMER_REF
927: from ra_interface_lines rai,
928: ra_customer_merges m
929: where nvl(rai.interface_status,'N') <> 'P'
930: and m.process_flag = 'N'
931: and m.request_id = req_id
932: and m.set_number = set_num
945: ,ORIG_SYSTEM_BILL_ADDRESS_REF
946: ,ORIG_SYSTEM_SHIP_ADDRESS_REF
947: ,m.CUSTOMER_REF
948: from ra_interface_lines rai,
949: ra_customer_merges m
950: where nvl(rai.interface_status,'N') <> 'P'
951: and m.process_flag = 'N'
952: and m.request_id = req_id
953: and m.set_number = set_num
966: ,ORIG_SYSTEM_BILL_ADDRESS_REF
967: ,ORIG_SYSTEM_SHIP_ADDRESS_REF
968: ,m.CUSTOMER_REF
969: from ra_interface_lines rai,
970: ra_customer_merges m
971: where nvl(rai.interface_status,'N') <> 'P'
972: and m.process_flag = 'N'
973: and m.request_id = req_id
974: and m.set_number = set_num
987: ,ORIG_SYSTEM_BILL_ADDRESS_REF
988: ,ORIG_SYSTEM_SHIP_ADDRESS_REF
989: ,m.CUSTOMER_REF
990: from ra_interface_lines rai,
991: ra_customer_merges m
992: where nvl(rai.interface_status,'N') <> 'P'
993: and m.process_flag = 'N'
994: and m.request_id = req_id
995: and m.set_number = set_num
1008: ,ORIG_SYSTEM_BILL_ADDRESS_REF
1009: ,ORIG_SYSTEM_SHIP_ADDRESS_REF
1010: ,m.CUSTOMER_REF
1011: from ra_interface_lines rai,
1012: ra_customer_merges m
1013: where nvl(rai.interface_status,'N') <> 'P'
1014: and m.process_flag = 'N'
1015: and m.request_id = req_id
1016: and m.set_number = set_num
1029: ,ORIG_SYSTEM_BILL_ADDRESS_REF
1030: ,ORIG_SYSTEM_SHIP_ADDRESS_REF
1031: ,m.CUSTOMER_REF
1032: from ra_interface_lines rai,
1033: ra_customer_merges m
1034: where nvl(rai.interface_status,'N') <> 'P'
1035: and m.process_flag = 'N'
1036: and m.request_id = req_id
1037: and m.set_number = set_num
1050: ,ORIG_SYSTEM_BILL_ADDRESS_REF
1051: ,ORIG_SYSTEM_SHIP_ADDRESS_REF
1052: ,m.CUSTOMER_REF
1053: from ra_interface_lines rai,
1054: ra_customer_merges m
1055: where nvl(rai.interface_status,'N') <> 'P'
1056: and m.process_flag = 'N'
1057: and m.request_id = req_id
1058: and m.set_number = set_num
1071: ,ORIG_SYSTEM_BILL_ADDRESS_REF
1072: ,ORIG_SYSTEM_SHIP_ADDRESS_REF
1073: ,m.CUSTOMER_REF
1074: from ra_interface_lines rai,
1075: ra_customer_merges m
1076: where nvl(rai.interface_status,'N') <> 'P'
1077: and m.process_flag = 'N'
1078: and m.request_id = req_id
1079: and m.set_number = set_num
1095: ,ORIG_SYSTEM_BILL_ADDRESS_REF
1096: ,ORIG_SYSTEM_SHIP_ADDRESS_REF
1097: ,m.CUSTOMER_REF
1098: from ra_interface_lines rai,
1099: ra_customer_merges m
1100: where nvl(rai.interface_status,'N') <> 'P'
1101: and m.process_flag = 'N'
1102: and m.request_id = req_id
1103: and m.set_number = set_num
1316: SELECT distinct CUSTOMER_MERGE_HEADER_ID
1317: ,line_id
1318: ,third_party_id
1319: ,third_party_sub_id
1320: FROM AR_DISTRIBUTIONS yt, ra_customer_merges m
1321: WHERE ( (yt.third_party_id = m.DUPLICATE_ID AND
1322: nvl(yt.third_party_sub_id,m.DUPLICATE_SITE_ID) = m.DUPLICATE_SITE_ID))
1323: AND m.process_flag = 'N'
1324: AND m.request_id = req_id