eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_CUSTOMER_INT dependencies on RA_CUSTOMERS_INTERFACE

Line 262: FROM ra_customers_interface;

258: WHERE orig_system_reference = p_orig_system_contact_ref;
259:
260: CURSOR c2 IS
261: SELECT orig_system_customer_ref
262: FROM ra_customers_interface;
263: BEGIN
264: OPEN c7;
265: FETCH c7 INTO l_party_id;
266: CLOSE c7;

Line 316: FROM ra_customers_interface i

312: CURSOR auto_acc IS
313: SELECT site_use_code, i.gl_id_rec, i.gl_id_rev, i.gl_id_tax,
314: i.gl_id_freight, i.gl_id_clearing, i.gl_id_unbilled,
315: i.gl_id_unearned, i.interface_status
316: FROM ra_customers_interface i
317: WHERE i.request_id = p_request_id
318: AND nvl(i.validated_flag,'N') <> 'Y'
319: AND (gl_id_rec IS NOT NULL
320: OR gl_id_rev IS NOT NULL

Line 331: FROM ra_customers_interface i

327:
328: CURSOR boe IS
329: SELECT i.site_use_code, i.gl_id_unpaid_rec, i.gl_id_remittance,
330: i.gl_id_factor, i.interface_status
331: FROM ra_customers_interface i
332: WHERE i.request_id = p_request_id
333: AND nvl(i.validated_flag,'N') <> 'Y'
334: AND (gl_id_unpaid_rec IS NOT NULL
335: OR gl_id_remittance IS NOT NULL

Line 450: UPDATE ra_customers_interface_all

446:
447: END IF;
448: END IF;
449:
450: UPDATE ra_customers_interface_all
451: SET interface_status = p_interface_status
452: WHERE CURRENT OF auto_acc;
453:
454: END LOOP;

Line 505: UPDATE ra_customers_interface_all

501:
502: END IF;
503: END IF;
504:
505: UPDATE ra_customers_interface_all
506: SET interface_status = p_interface_status
507: WHERE CURRENT OF boe;
508: END LOOP;
509: CLOSE boe;

Line 738: from ra_customers_interface i,

734: WHEN NO_DATA_FOUND THEN
735: BEGIN
736: select 'X'
737: into l_count
738: from ra_customers_interface i,
739: ra_customers_interface i1
740: where
741: i.request_id = req_id
742: and i.bill_to_orig_address_ref is not NULL

Line 739: ra_customers_interface i1

735: BEGIN
736: select 'X'
737: into l_count
738: from ra_customers_interface i,
739: ra_customers_interface i1
740: where
741: i.request_id = req_id
742: and i.bill_to_orig_address_ref is not NULL
743: and i1.site_use_code = 'BILL_TO'

Line 809: FROM ra_customers_interface i,

805: AND site.status = 'A';
806:
807: CURSOR c1 IS -- If the customer or related customer do not exist THEN Check for their existence in the interface table
808: SELECT 'x'
809: FROM ra_customers_interface i,
810: ra_customers_interface_all i1 -- bug 4454799
811: WHERE i.request_id = req_id
812: AND i.bill_to_orig_address_ref is not NULL
813: AND i.orig_system_customer_ref = p_orig_system_customer_ref

Line 810: ra_customers_interface_all i1 -- bug 4454799

806:
807: CURSOR c1 IS -- If the customer or related customer do not exist THEN Check for their existence in the interface table
808: SELECT 'x'
809: FROM ra_customers_interface i,
810: ra_customers_interface_all i1 -- bug 4454799
811: WHERE i.request_id = req_id
812: AND i.bill_to_orig_address_ref is not NULL
813: AND i.orig_system_customer_ref = p_orig_system_customer_ref
814: AND i.orig_system_address_ref = p_orig_system_address_ref

Line 824: FROM ra_customers_interface i,

820: AND i1.interface_status is null
821: AND rownum = 1
822: UNION ALL
823: SELECT 'x'
824: FROM ra_customers_interface i,
825: ra_customers_interface_all i1 -- bug 4454799
826: WHERE i.request_id = req_id
827: AND i.bill_to_orig_address_ref is not NULL
828: AND i.orig_system_customer_ref = p_orig_system_customer_ref

Line 825: ra_customers_interface_all i1 -- bug 4454799

821: AND rownum = 1
822: UNION ALL
823: SELECT 'x'
824: FROM ra_customers_interface i,
825: ra_customers_interface_all i1 -- bug 4454799
826: WHERE i.request_id = req_id
827: AND i.bill_to_orig_address_ref is not NULL
828: AND i.orig_system_customer_ref = p_orig_system_customer_ref
829: AND i1.orig_system_customer_ref = i.orig_system_parent_ref

Line 908: l_orig_system_party_ref ra_customers_interface.orig_system_party_ref%TYPE;

904:
905: FUNCTION get_ultimate_parent_party_ref (p_orig_system_customer_ref VARCHAR2)
906: RETURN VARCHAR2 IS
907:
908: l_orig_system_party_ref ra_customers_interface.orig_system_party_ref%TYPE;
909: l_orig_system_parent_ref ra_customers_interface.orig_system_parent_ref%TYPE;
910:
911: -- bug 2098243 - fixed to use a cursor instead of a direct select.
912: CURSOR c10 IS

Line 909: l_orig_system_parent_ref ra_customers_interface.orig_system_parent_ref%TYPE;

905: FUNCTION get_ultimate_parent_party_ref (p_orig_system_customer_ref VARCHAR2)
906: RETURN VARCHAR2 IS
907:
908: l_orig_system_party_ref ra_customers_interface.orig_system_party_ref%TYPE;
909: l_orig_system_parent_ref ra_customers_interface.orig_system_parent_ref%TYPE;
910:
911: -- bug 2098243 - fixed to use a cursor instead of a direct select.
912: CURSOR c10 IS
913: SELECT orig_system_parent_ref, orig_system_party_ref

Line 914: FROM ra_customers_interface

910:
911: -- bug 2098243 - fixed to use a cursor instead of a direct select.
912: CURSOR c10 IS
913: SELECT orig_system_parent_ref, orig_system_party_ref
914: FROM ra_customers_interface
915: WHERE orig_system_customer_ref = p_orig_system_customer_ref
916: AND ROWNUM = 1;
917: BEGIN
918: OPEN c10;

Line 1298: FROM ra_customers_interface

1294: l_dummy VARCHAR(1);
1295:
1296: CURSOR c1 IS -- Check if Cust record exists for ninsertion
1297: SELECT 'x'
1298: FROM ra_customers_interface
1299: WHERE orig_system_customer_ref = v_orig_system_customer_ref
1300: AND interface_status is null
1301: AND request_id = v_request_id;
1302:

Line 1324: FROM ra_customers_interface

1320: AND rsu.site_use_code in ('BILL_TO','DUN','STMTS')
1321: UNION ALL
1322: SELECT 'x' -- If not already defined, THEN address rec should
1323: -- exist in interface table with Bill To
1324: FROM ra_customers_interface
1325: WHERE orig_system_customer_ref = v_orig_system_customer_ref
1326: AND interface_status is null
1327: AND orig_system_address_ref = v_orig_system_address_ref
1328: AND org_id = v_org_id -- bug 4454799

Line 1661: FROM ra_customers_interface_all i

1657: WHERE party.party_number = p_party_number;
1658:
1659: CURSOR c2 IS -- Check if the party_number already exists in interface table
1660: SELECT decode(NVL(i.orig_system_party_ref,i.orig_system_customer_ref),p_orig_system_party_ref,'','Y3,')
1661: FROM ra_customers_interface_all i
1662: WHERE i.party_number = p_party_number
1663: AND i.request_id = req_id
1664: AND i.rowid <> p_rowid ;
1665:

Line 1668: FROM ra_customers_interface_all i

1664: AND i.rowid <> p_rowid ;
1665:
1666: CURSOR c3 IS -- Check if the party_number already exists in interface table
1667: SELECT decode(NVL(i.orig_system_party_ref,i.orig_system_customer_ref),p_orig_system_customer_ref,'','Y3,')
1668: FROM ra_customers_interface_all i
1669: WHERE i.party_number = p_party_number
1670: AND i.request_id = req_id
1671: AND i.rowid <> p_rowid ;
1672:

Line 1774: FROM ra_customers_interface_all i

1770:
1771:
1772: CURSOR c IS -- Check if the same party has a diff party_number
1773: SELECT decode(i.party_number,p_party_number,'','Y4,')
1774: FROM ra_customers_interface_all i
1775: WHERE i.orig_system_party_ref = p_orig_system_party_ref
1776: AND i.request_id = req_id
1777: AND i.rowid <> p_rowid ;
1778:

Line 1781: FROM ra_customers_interface_all i

1777: AND i.rowid <> p_rowid ;
1778:
1779: CURSOR c1 IS -- Check if the same party has a diff party_number and the orig_system_party_ref is null
1780: SELECT decode(i.party_number,p_party_number,'','Y4,')
1781: FROM ra_customers_interface_all i
1782: WHERE i.orig_system_customer_ref = p_orig_system_party_ref
1783: AND i.orig_system_party_ref is null
1784: AND i.request_id = req_id
1785: AND i.rowid <> p_rowid ;

Line 1789: FROM ra_customers_interface_all i

1785: AND i.rowid <> p_rowid ;
1786:
1787: CURSOR c2 IS -- Check if the same party has a diff party_number and the orig_system_party_ref passed is null
1788: SELECT decode(i.party_number,p_party_number,'','Y4,')
1789: FROM ra_customers_interface_all i
1790: WHERE i.orig_system_party_ref = p_orig_system_customer_ref
1791: AND i.request_id = req_id
1792: AND i.rowid <> p_rowid ;
1793:

Line 1796: FROM ra_customers_interface_all i

1792: AND i.rowid <> p_rowid ;
1793:
1794: CURSOR c3 IS -- Check if the same party has a diff party_number and the orig_system_party_ref passed is null and the record in the interface table also has orig_system_party_ref as null
1795: SELECT decode(i.party_number,p_party_number,'','Y4,')
1796: FROM ra_customers_interface_all i
1797: WHERE i.orig_system_customer_ref = p_orig_system_customer_ref
1798: AND i.orig_system_party_ref is null
1799: AND i.request_id = req_id
1800: AND i.rowid <> p_rowid ;

Line 1906: FROM ra_customers_interface_all i

1902: WHERE cust.account_number = p_customer_number;
1903:
1904: CURSOR c1 IS -- Check if the customer_number already exists in the interface table
1905: SELECT decode(i.orig_system_customer_ref,p_orig_system_customer_ref,'','A5,')
1906: FROM ra_customers_interface_all i
1907: WHERE i.customer_number = p_customer_number
1908: AND i.request_id = req_id
1909: AND i.rowid <> p_rowid ;
1910:

Line 1989: FROM ra_customers_interface_all i

1985: AND site.party_site_id = cust_site.party_site_id;
1986:
1987: CURSOR c1 IS -- Check if the party_site_number exists in the interface table
1988: SELECT decode(i.orig_system_address_ref,p_orig_system_address_ref,'','Y6,')
1989: FROM ra_customers_interface_all i
1990: WHERE i.party_site_number = p_party_site_number
1991: AND i.org_id = p_org_id -- bug 4454799
1992: AND i.request_id = req_id
1993: AND i.rowid <> p_rowid ;

Line 2357: ra_customers_interface_all i, -- Bug 4956131

2353: i.cust_tax_reference,
2354: decode(sst.tax_reference, '', 'Y', 'N'),nvl(org.version_number,1)+1
2355: FROM hz_organization_profiles org,
2356: hz_organization_profiles sst,
2357: ra_customers_interface_all i, -- Bug 4956131
2358: hz_cust_accounts cust,
2359: (SELECT min(i1.rowid) myrowid
2360: FROM ra_customers_interface_all i1 -- Bug 4956131
2361: WHERE i1.request_id = p_request_id

Line 2360: FROM ra_customers_interface_all i1 -- Bug 4956131

2356: hz_organization_profiles sst,
2357: ra_customers_interface_all i, -- Bug 4956131
2358: hz_cust_accounts cust,
2359: (SELECT min(i1.rowid) myrowid
2360: FROM ra_customers_interface_all i1 -- Bug 4956131
2361: WHERE i1.request_id = p_request_id
2362: AND i1.interface_status IS NULL
2363: AND i1.insert_update_flag='U'
2364: AND NVL(i1.person_flag,'N') = 'N'

Line 2680: ra_customers_interface_all i, -- Bug 4956131

2676: i.cust_tax_reference,
2677: decode(sst.tax_reference, '', 'Y', 'N'),nvl(per.version_number,1)+1
2678: FROM hz_person_profiles per,
2679: hz_person_profiles sst,
2680: ra_customers_interface_all i, -- Bug 4956131
2681: hz_cust_accounts cust,
2682: (SELECT min(i1.rowid) myrowid
2683: FROM ra_customers_interface_all i1 -- Bug 4956131
2684: WHERE i1.request_id = p_request_id

Line 2683: FROM ra_customers_interface_all i1 -- Bug 4956131

2679: hz_person_profiles sst,
2680: ra_customers_interface_all i, -- Bug 4956131
2681: hz_cust_accounts cust,
2682: (SELECT min(i1.rowid) myrowid
2683: FROM ra_customers_interface_all i1 -- Bug 4956131
2684: WHERE i1.request_id = p_request_id
2685: AND i1.interface_status IS NULL
2686: AND i1.insert_update_flag='U'
2687: AND i1.person_flag = 'Y'

Line 3169: FROM HZ_PARTIES party, ra_customers_interface_all rci -- Bug 4956131

3165: FND_GLOBAL.User_ID CREATED_BY,
3166: SYSDATE LAST_UPDATE_DATE,
3167: FND_GLOBAL.User_ID LAST_UPDATED_BY,
3168: FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
3169: FROM HZ_PARTIES party, ra_customers_interface_all rci -- Bug 4956131
3170: WHERE party.orig_system_reference = nvl(rci.orig_system_party_ref, rci.orig_system_customer_ref)
3171: AND party.request_id = p_request_id
3172: AND rci.interface_status is null
3173: AND rci.request_id = p_request_id

Line 3176: FROM ra_customers_interface_all i2 -- Bug 4956131

3172: AND rci.interface_status is null
3173: AND rci.request_id = p_request_id
3174: AND rci.insert_update_flag = 'I'
3175: AND (rci.rowid = ( SELECT min(i2.rowid)
3176: FROM ra_customers_interface_all i2 -- Bug 4956131
3177: WHERE i2.orig_system_customer_ref = rci.orig_system_customer_ref
3178: AND rci.orig_system_party_ref is null
3179: AND i2.interface_status is null
3180: AND i2.request_id = p_request_id

Line 3183: FROM ra_customers_interface_all i2 -- Bug 4956131

3179: AND i2.interface_status is null
3180: AND i2.request_id = p_request_id
3181: AND i2.insert_update_flag = 'I') OR
3182: rci.rowid = ( SELECT min(i2.rowid)
3183: FROM ra_customers_interface_all i2 -- Bug 4956131
3184: WHERE i2.orig_system_party_ref = rci.orig_system_party_ref
3185: AND i2.interface_status is null
3186: AND i2.request_id = p_request_id
3187: AND i2.insert_update_flag = 'I'))

Line 3317: FROM HZ_PARTY_SITES ps, ra_customers_interface_all rci, -- Bug 4956131

3313: FND_GLOBAL.User_ID CREATED_BY,
3314: SYSDATE LAST_UPDATE_DATE,
3315: FND_GLOBAL.User_ID LAST_UPDATED_BY,
3316: FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
3317: FROM HZ_PARTY_SITES ps, ra_customers_interface_all rci, -- Bug 4956131
3318: HZ_LOCATIONS loc --4742586
3319: WHERE ps.orig_system_reference = rci.orig_system_address_ref
3320: AND loc.location_id = ps.location_id --4742586
3321: AND ps.request_id = p_request_id

Line 3326: FROM ra_customers_interface_all i2 -- Bug 4956131

3322: AND rci.interface_status is null
3323: AND rci.request_id = p_request_id
3324: AND rci.insert_update_flag = 'I'
3325: AND (rci.rowid = ( SELECT min(i2.rowid)
3326: FROM ra_customers_interface_all i2 -- Bug 4956131
3327: WHERE i2.orig_system_address_ref = rci.orig_system_address_ref
3328: AND i2.interface_status is null
3329: AND i2.request_id = p_request_id
3330: AND i2.insert_update_flag = 'I'))) PTY

Line 3432: from ra_customers_interface rci,

3428: ,hz_utility_v2pub.last_updated_by -- LAST_UPDATED_BY
3429: ,p_request_id -- REQUEST_ID
3430: ,hz_utility_v2pub.program_application_id -- PROGRAM_APPLICATION_ID
3431: ,hz_utility_v2pub.program_id -- PROGRAM_ID
3432: from ra_customers_interface rci,
3433: hz_parties hzp
3434: WHERE hzp.orig_system_reference = nvl(rci.orig_system_party_ref, rci.orig_system_customer_ref)
3435: and hzp.request_id = p_request_id
3436: AND rci.interface_status is null

Line 3439: FROM ra_customers_interface i2

3435: and hzp.request_id = p_request_id
3436: AND rci.interface_status is null
3437: AND rci.insert_update_flag = 'I'
3438: AND ( rci.rowid = (SELECT min(i2.rowid)
3439: FROM ra_customers_interface i2
3440: WHERE i2.orig_system_customer_ref =
3441: rci.orig_system_customer_ref
3442: and rci.orig_system_party_ref is null
3443: AND i2.interface_status is null

Line 3446: FROM ra_customers_interface i2

3442: and rci.orig_system_party_ref is null
3443: AND i2.interface_status is null
3444: AND i2.insert_update_flag = 'I') OR
3445: rci.rowid = (SELECT min(i2.rowid)
3446: FROM ra_customers_interface i2
3447: WHERE i2.orig_system_party_ref = rci.orig_system_party_ref
3448: AND i2.interface_status is null
3449: AND i2.insert_update_flag = 'I')
3450: );

Line 3508: from ra_customers_interface_all rci, -- Bug 4956131

3504: ,hz_utility_v2pub.last_updated_by -- LAST_UPDATED_BY
3505: ,p_request_id -- REQUEST_ID
3506: ,hz_utility_v2pub.program_application_id-- PROGRAM_APPLICATION_ID
3507: ,hz_utility_v2pub.program_id -- PROGRAM_ID
3508: from ra_customers_interface_all rci, -- Bug 4956131
3509: hz_parties hzp
3510: WHERE hzp.party_id = HZ_CUSTOMER_INT.get_account_party_id(rci.orig_system_party_ref,rci.person_flag,'P')
3511: AND rci.request_id = p_request_id
3512: AND rci.interface_status is null

Line 3515: FROM ra_customers_interface_all i2 -- Bug 4956131

3511: AND rci.request_id = p_request_id
3512: AND rci.interface_status is null
3513: AND rci.insert_update_flag = 'I'
3514: AND ( rci.rowid = (SELECT min(i2.rowid)
3515: FROM ra_customers_interface_all i2 -- Bug 4956131
3516: WHERE i2.orig_system_customer_ref =
3517: rci.orig_system_customer_ref
3518: and rci.orig_system_party_ref is null
3519: AND i2.interface_status is null

Line 3523: FROM ra_customers_interface_all i2 -- Bug 4956131

3519: AND i2.interface_status is null
3520: AND i2.request_id = p_request_id
3521: AND i2.insert_update_flag = 'I') OR
3522: rci.rowid = (SELECT min(i2.rowid)
3523: FROM ra_customers_interface_all i2 -- Bug 4956131
3524: WHERE i2.orig_system_party_ref = rci.orig_system_party_ref
3525: AND i2.request_id = p_request_id
3526: AND i2.interface_status is null
3527: AND i2.insert_update_flag = 'I')

Line 3677: FROM ra_customers_interface

3673: IF p_mode = 'VALIDATE' THEN
3674:
3675: BEGIN
3676: SELECT cust_tax_reference INTO l_cust_tax_reference
3677: FROM ra_customers_interface
3678: WHERE request_id = p_request_id
3679: AND cust_tax_reference IS NOT NULL
3680: AND ROWNUM = 1;
3681:

Line 3693: l_sql_from_insert_ptp_intf := ' FROM ra_customers_interface_all hz_rcia'||

3689: l_sql_select_insert_ptp_intf := 'SELECT cust_tax_reference,country,''CREATE'''||
3690: ',Nvl(orig_system_party_ref,orig_system_customer_ref) AS intf_party_reference'||
3691: ',NULL AS intf_party_site_reference'||
3692: ',''THIRD_PARTY''';
3693: l_sql_from_insert_ptp_intf := ' FROM ra_customers_interface_all hz_rcia'||
3694: ' WHERE hz_rcia.insert_update_flag = ''I'''||
3695: ' AND hz_rcia.request_id = '||p_request_id||
3696: ' AND hz_rcia.cust_tax_reference IS NOT NULL';
3697:

Line 3711: ,p_interface_table_name => 'RA_CUSTOMERS_INTERFACE_ALL'

3707:
3708: FND_FILE.put_line(fnd_file.log,SYSDATE||' Call ZX_PTP_IMPORT.IMPORT_WRAPPER for VALIDATE with request_id :'||p_request_id);
3709:
3710: ZX_PTP_IMPORT.IMPORT_WRAPPER(p_request_id => p_request_id
3711: ,p_interface_table_name => 'RA_CUSTOMERS_INTERFACE_ALL'
3712: ,p_gather_stats => FND_API.G_TRUE
3713: , p_validate_only => FND_API.G_TRUE
3714: , p_sql_select_insert_ptp_intf => l_sql_select_insert_ptp_intf
3715: , p_sql_from_insert_ptp_intf => l_sql_from_insert_ptp_intf

Line 3740: /* Update error status for all the errored out customers in ra_customers_interface_all*/

3736:
3737: OPEN error_parties;
3738: FETCH error_parties BULK COLLECT INTO l_party_orig_sys_ref;
3739:
3740: /* Update error status for all the errored out customers in ra_customers_interface_all*/
3741:
3742: FORALL i IN 1..l_party_orig_sys_ref.Count
3743:
3744: UPDATE ra_customers_interface_all rci

Line 3744: UPDATE ra_customers_interface_all rci

3740: /* Update error status for all the errored out customers in ra_customers_interface_all*/
3741:
3742: FORALL i IN 1..l_party_orig_sys_ref.Count
3743:
3744: UPDATE ra_customers_interface_all rci
3745: SET interface_status = interface_status|| 'z4,'
3746: WHERE request_id = p_request_id
3747: AND Nvl(orig_system_party_ref,orig_system_customer_ref) = l_party_orig_sys_ref(i);
3748:

Line 3749: /* Update error status for all the errored out addresses in ra_customers_interface_all*/

3745: SET interface_status = interface_status|| 'z4,'
3746: WHERE request_id = p_request_id
3747: AND Nvl(orig_system_party_ref,orig_system_customer_ref) = l_party_orig_sys_ref(i);
3748:
3749: /* Update error status for all the errored out addresses in ra_customers_interface_all*/
3750:
3751: CLOSE error_parties;
3752:
3753: OPEN error_sites;

Line 3758: UPDATE ra_customers_interface_all rci

3754: FETCH error_sites BULK COLLECT INTO l_address_orig_sys_ref;
3755:
3756: FORALL i IN 1..l_address_orig_sys_ref.Count
3757:
3758: UPDATE ra_customers_interface_all rci
3759: SET interface_status = interface_status|| 'z5,'
3760: WHERE request_id = p_request_id
3761: AND orig_system_address_ref = l_address_orig_sys_ref(i);
3762: CLOSE error_sites;

Line 3775: ' FROM HZ_PARTIES hz_insert, ra_customers_interface hz_rcia'||

3771:
3772: l_sql_from_valid_party_id := ' FROM ((SELECT hz_insert.party_id, ''THIRD_PARTY'' as party_type_code'||
3773: ',hz_insert.orig_system_reference as intf_party_reference'||
3774: ',NULL as intf_party_site_reference'||
3775: ' FROM HZ_PARTIES hz_insert, ra_customers_interface hz_rcia'||
3776: ' WHERE hz_insert.orig_system_reference = nvl(hz_rcia.orig_system_party_ref, hz_rcia.orig_system_customer_ref)'||
3777: ' AND hz_insert.request_id = hz_rcia.request_id'||
3778: ' AND hz_rcia.interface_status is null'||
3779: ' AND hz_rcia.insert_update_flag = ''I'''||

Line 3781: ' FROM ra_customers_interface i2'||

3777: ' AND hz_insert.request_id = hz_rcia.request_id'||
3778: ' AND hz_rcia.interface_status is null'||
3779: ' AND hz_rcia.insert_update_flag = ''I'''||
3780: ' AND (hz_rcia.rowid = ( SELECT min(i2.rowid)'||
3781: ' FROM ra_customers_interface i2'||
3782: ' WHERE i2.orig_system_customer_ref = hz_rcia.orig_system_customer_ref'||
3783: ' AND i2.request_id = hz_rcia.request_id'||
3784: ' AND hz_rcia.orig_system_party_ref is null'||
3785: ' AND i2.interface_status is null'||

Line 3788: ' FROM ra_customers_interface_all i2'||

3784: ' AND hz_rcia.orig_system_party_ref is null'||
3785: ' AND i2.interface_status is null'||
3786: ' AND i2.insert_update_flag = ''I'') OR'||
3787: ' hz_rcia.rowid = ( SELECT min(i2.rowid)'||
3788: ' FROM ra_customers_interface_all i2'||
3789: ' WHERE i2.orig_system_party_ref = hz_rcia.orig_system_party_ref'||
3790: ' AND i2.request_id = hz_rcia.request_id'||
3791: ' AND i2.interface_status is null'||
3792: ' AND i2.insert_update_flag = ''I''))'||

Line 3798: ' FROM HZ_PARTY_SITES ps, ra_customers_interface rci'||

3794: ' UNION ALL ' ||
3795: '(SELECT ps.party_site_id as party_id, ''THIRD_PARTY_SITE'' as party_type_code'||
3796: ', NVL(rci.orig_system_party_ref, rci.orig_system_customer_ref) as intf_party_reference'||
3797: ',ps.orig_system_reference as intf_party_site_reference'||
3798: ' FROM HZ_PARTY_SITES ps, ra_customers_interface rci'||
3799: ' WHERE ps.orig_system_reference = rci.orig_system_address_ref'||
3800: ' AND ps.request_id = rci.request_id'||
3801: ' AND rci.interface_status is null' ||
3802: ' AND rci.insert_update_flag = ''I'''||

Line 3804: ' FROM ra_customers_interface i2'||

3800: ' AND ps.request_id = rci.request_id'||
3801: ' AND rci.interface_status is null' ||
3802: ' AND rci.insert_update_flag = ''I'''||
3803: ' AND (rci.rowid = ( SELECT min(i2.rowid)'||
3804: ' FROM ra_customers_interface i2'||
3805: ' WHERE i2.orig_system_address_ref = rci.orig_system_address_ref'||
3806: ' AND i2.request_id = rci.request_id'||
3807: ' AND i2.interface_status is NULL'||
3808: ' AND i2.insert_update_flag = ''I''))) '||

Line 3814: ' FROM HZ_PARTIES hz_insert, ra_customers_interface hz_rcia'||

3810:
3811: l_sql_select_insert_ptp_prod := 'SELECT party_id, party_type_code, country country_code';
3812:
3813: l_sql_from_insert_ptp_prod := ' FROM ((SELECT hz_insert.party_id, ''THIRD_PARTY'' as party_type_code, hz_insert.country'||
3814: ' FROM HZ_PARTIES hz_insert, ra_customers_interface hz_rcia'||
3815: ' WHERE hz_insert.orig_system_reference = nvl(hz_rcia.orig_system_party_ref, hz_rcia.orig_system_customer_ref)'||
3816: ' AND hz_insert.request_id = hz_rcia.request_id'||
3817: ' AND hz_rcia.interface_status is null'||
3818: ' AND hz_rcia.insert_update_flag = ''I'''||

Line 3820: ' FROM ra_customers_interface i2'||

3816: ' AND hz_insert.request_id = hz_rcia.request_id'||
3817: ' AND hz_rcia.interface_status is null'||
3818: ' AND hz_rcia.insert_update_flag = ''I'''||
3819: ' AND (hz_rcia.rowid = ( SELECT min(i2.rowid)'||
3820: ' FROM ra_customers_interface i2'||
3821: ' WHERE i2.orig_system_customer_ref = hz_rcia.orig_system_customer_ref'||
3822: ' AND i2.request_id = hz_rcia.request_id'||
3823: ' AND hz_rcia.orig_system_party_ref is null'||
3824: ' AND i2.interface_status is null'||

Line 3827: ' FROM ra_customers_interface i2'||

3823: ' AND hz_rcia.orig_system_party_ref is null'||
3824: ' AND i2.interface_status is null'||
3825: ' AND i2.insert_update_flag = ''I'') OR'||
3826: ' hz_rcia.rowid = ( SELECT min(i2.rowid)'||
3827: ' FROM ra_customers_interface i2'||
3828: ' WHERE i2.orig_system_party_ref = hz_rcia.orig_system_party_ref'||
3829: ' AND i2.request_id = hz_rcia.request_id'||
3830: ' AND i2.interface_status is null'||
3831: ' AND i2.insert_update_flag = ''I''))'||

Line 3835: ' FROM HZ_PARTY_SITES ps, ra_customers_interface rci, HZ_LOCATIONS loc '||

3831: ' AND i2.insert_update_flag = ''I''))'||
3832: ' AND (hz_insert.party_type =''ORGANIZATION'' OR hz_insert.party_type =''PERSON''))'||
3833: ' UNION ALL ' ||
3834: '(SELECT ps.party_site_id as party_id, ''THIRD_PARTY_SITE'' as party_type_code, loc.country'||
3835: ' FROM HZ_PARTY_SITES ps, ra_customers_interface rci, HZ_LOCATIONS loc '||
3836: ' WHERE ps.orig_system_reference = rci.orig_system_address_ref'||
3837: ' AND loc.location_id = ps.location_id '||
3838: ' AND ps.request_id = rci.request_id'||
3839: ' AND rci.interface_status is null' ||

Line 3842: ' FROM ra_customers_interface i2'||

3838: ' AND ps.request_id = rci.request_id'||
3839: ' AND rci.interface_status is null' ||
3840: ' AND rci.insert_update_flag = ''I'''||
3841: ' AND (rci.rowid = ( SELECT min(i2.rowid)'||
3842: ' FROM ra_customers_interface i2'||
3843: ' WHERE i2.orig_system_address_ref = rci.orig_system_address_ref'||
3844: ' AND i2.interface_status is NULL'||
3845: ' AND i2.request_id = rci.request_id'||
3846: ' AND i2.insert_update_flag = ''I'')))'||

Line 3875: FROM ra_customers_interface

3871:
3872: SELECT request_id INTO l_request_id
3873: FROM zx_party_tax_profile_int
3874: WHERE intf_party_reference IN (SELECT Nvl(orig_system_party_ref, orig_system_customer_ref)
3875: FROM ra_customers_interface
3876: )
3877: AND ROWNUM = 1 ;
3878:
3879: EXCEPTION

Line 3889: ,p_interface_table_name => 'RA_CUSTOMERS_INTERFACE_ALL'

3885:
3886: FND_FILE.put_line(fnd_file.log,'['||To_Char(SYSDATE,'DD-MON-RRRR HH24:MI:SS')||'] '||' Call ZX_PTP_IMPORT.IMPORT_WRAPPER for INSERT with request_id :'||l_request_id);
3887:
3888: ZX_PTP_IMPORT.IMPORT_WRAPPER(p_request_id => l_request_id
3889: ,p_interface_table_name => 'RA_CUSTOMERS_INTERFACE_ALL'
3890: ,p_gather_stats => FND_API.G_FALSE
3891: , p_validate_only => FND_API.G_FALSE
3892: , p_sql_select_insert_ptp_intf => NULL
3893: , p_sql_from_insert_ptp_intf => NULL

Line 3920: FROM ra_customers_interface

3916:
3917: SELECT request_id INTO l_request_id
3918: FROM zx_party_tax_profile_int
3919: WHERE intf_party_reference IN (SELECT Nvl(orig_system_party_ref, orig_system_customer_ref)
3920: FROM ra_customers_interface
3921: )
3922: AND ROWNUM = 1 ;
3923:
3924: EXCEPTION

Line 3936: ,p_interface_table_name => 'RA_CUSTOMERS_INTERFACE_ALL'

3932: FND_FILE.put_line(fnd_file.log,'l_request_id -'||l_request_id);
3933: FND_FILE.put_line(fnd_file.output,'');
3934:
3935: ZX_PTP_IMPORT.MESSAGE_WRAPPER(p_request_id => l_request_id
3936: ,p_interface_table_name => 'RA_CUSTOMERS_INTERFACE_ALL'
3937: ,p_display_stats => FND_API.G_TRUE
3938: ,p_display_messages => FND_API.G_FALSE
3939: ,p_message_header_string_1 => NULL
3940: ,p_message_header_string_2 => NULL

Line 3956: FROM ra_customers_interface

3952:
3953: SELECT request_id INTO l_request_id
3954: FROM zx_party_tax_profile_int
3955: WHERE intf_party_reference IN (SELECT Nvl(orig_system_party_ref, orig_system_customer_ref)
3956: FROM ra_customers_interface
3957: )
3958: AND ROWNUM = 1 ;
3959:
3960: EXCEPTION

Line 3974: ,p_interface_table_name => 'RA_CUSTOMERS_INTERFACE_ALL'

3970: FND_FILE.put_line(fnd_file.log,'l_request_id -'||l_request_id);
3971:
3972:
3973: ZX_PTP_IMPORT.MESSAGE_WRAPPER(p_request_id => l_request_id
3974: ,p_interface_table_name => 'RA_CUSTOMERS_INTERFACE_ALL'
3975: ,p_display_stats => FND_API.G_FALSE
3976: ,p_display_messages => FND_API.G_TRUE
3977: ,p_message_header_string_1 => l_message_header_string_1
3978: ,p_message_header_string_2 => l_message_header_string_2

Line 3998: FROM ra_customers_interface

3994:
3995: SELECT request_id INTO l_request_id
3996: FROM zx_party_tax_profile_int
3997: WHERE intf_party_reference IN (SELECT Nvl(orig_system_party_ref, orig_system_customer_ref)
3998: FROM ra_customers_interface
3999: )
4000: AND ROWNUM = 1 ;
4001:
4002: EXCEPTION

Line 4038: DELETE FROM ra_customers_interface

4034: BEGIN
4035:
4036: FND_FILE.put_line(fnd_file.log,'HZ_CUSTOMER_INT.delete_success_records +');
4037:
4038: DELETE FROM ra_customers_interface
4039: WHERE interface_status is NULL;
4040:
4041: DELETE FROM RA_CONTACT_PHONES_INTERFACE
4042: WHERE interface_status is null;

Line 4055: UPDATE ra_customers_interface

4051: WHERE interface_status is null;
4052:
4053: /* reset request id to null */
4054:
4055: UPDATE ra_customers_interface
4056: SET request_id = NULL;
4057:
4058: UPDATE ra_customer_profiles_interface
4059: SET request_id = NULL;