[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_last_update_login NUMBER;
g_last_updated_by NUMBER;
SELECT cust_account_id
FROM hz_cust_accounts
WHERE orig_system_reference = p_orig_system_customer_ref;
SELECT cust_acct_site_id
FROM hz_cust_acct_sites_all -- bug 4454799
WHERE orig_system_reference = p_orig_system_address_ref
AND org_id = p_org_id; -- bug 4454799
SELECT cust_account_role_id
FROM hz_cust_account_roles
WHERE orig_system_reference = p_orig_system_contact_ref;
SELECT party_id
FROM hz_cust_account_roles
WHERE orig_system_reference = p_orig_system_contact_ref;
SELECT party_number
FROM hz_parties
WHERE orig_system_reference = 'PREL-'||p_orig_system_contact_ref;
SELECT party_name
FROM hz_parties
WHERE orig_system_reference = p_orig_system_contact_ref;
SELECT party_name
FROM hz_parties
WHERE party_id = (select party_id
from hz_cust_accounts
where orig_system_reference = p_orig_system_customer_ref);
PROCEDURE update_party_prel_name(p_party_id IN NUMBER )
IS
l_party_name VARCHAR2(360);
SELECT r.party_id, r.object_id, o.party_name, r.subject_id, s.party_name,
rel.party_number, rel.party_name
FROM hz_relationships r, hz_parties s, hz_parties o, hz_parties rel
WHERE (r.subject_id = p_party_id OR r.object_id = p_party_id)
AND r.party_id IS NOT NULL
AND r.subject_table_name = 'HZ_PARTIES'
AND r.object_table_name = 'HZ_PARTIES'
AND r.directional_flag = 'F'
AND r.subject_id = s.party_id
AND r.object_id = o.party_id
AND r.party_id = rel.party_id;
UPDATE hz_parties
SET party_name = l_party_name
WHERE party_id = i_party_id(i);
update_party_prel_name(i_party_id(i));
end update_party_prel_name;
SELECT party_site_id
FROM hz_cust_acct_sites_all -- bug 4454799
WHERE orig_system_reference = p_orig_system_address_ref
AND org_id = p_org_id; -- bug 4454799
SELECT party_id
FROM hz_cust_accounts
WHERE orig_system_reference = p_orig_system_customer_ref;
SELECT party_id
FROM hz_parties
WHERE orig_system_reference = p_orig_system_contact_ref;
SELECT orig_system_customer_ref
FROM ra_customers_interface;
SELECT language_code
FROM fnd_languages
WHERE nls_language = p_language;
SELECT site_use_code, i.gl_id_rec, i.gl_id_rev, i.gl_id_tax,
i.gl_id_freight, i.gl_id_clearing, i.gl_id_unbilled,
i.gl_id_unearned, i.interface_status
FROM ra_customers_interface i
WHERE i.request_id = p_request_id
AND nvl(i.validated_flag,'N') <> 'Y'
AND (gl_id_rec IS NOT NULL
OR gl_id_rev IS NOT NULL
OR gl_id_tax IS NOT NULL
OR gl_id_freight IS NOT NULL
OR gl_id_clearing IS NOT NULL
OR gl_id_unbilled IS NOT NULL
OR gl_id_unearned IS NOT NULL)
FOR UPDATE;
SELECT i.site_use_code, i.gl_id_unpaid_rec, i.gl_id_remittance,
i.gl_id_factor, i.interface_status
FROM ra_customers_interface i
WHERE i.request_id = p_request_id
AND nvl(i.validated_flag,'N') <> 'Y'
AND (gl_id_unpaid_rec IS NOT NULL
OR gl_id_remittance IS NOT NULL
OR gl_id_factor IS NOT NULL)
FOR UPDATE;
UPDATE ra_customers_interface_all
SET interface_status = p_interface_status
WHERE CURRENT OF auto_acc;
UPDATE ra_customers_interface_all
SET interface_status = p_interface_status
WHERE CURRENT OF boe;
p_insert_update_flag IN VARCHAR2)
RETURN VARCHAR2 IS
l_return_code VARCHAR2(5) := 'A3,';
SELECT party_id
FROM hz_parties
WHERE orig_system_reference = p_orig_system_customer_ref;
IF p_insert_update_flag = 'I' THEN
OPEN c7;
SELECT party_id
FROM hz_parties
WHERE orig_system_reference = p_orig_system_customer_ref
AND party_id = p_request_id /* Bug Fix : 5214454 */
-- AND request_id = p_request_id; /* Bug Fix : 1891773 */
SELECT party_id
FROM hz_parties party
WHERE party.orig_system_reference = p_orig_system_customer_ref
AND party.PARTY_TYPE = decode(p_person_flag, 'Y', 'PERSON','ORGANIZATION')
AND exists (select 'X' from hz_cust_accounts where party_id = party.party_id)
AND rownum = 1;
SELECT party_id
FROM hz_cust_accounts
WHERE orig_system_reference = p_orig_system_customer_ref;
SELECT party_id
FROM hz_parties
WHERE orig_system_reference = p_orig_system_customer_ref
AND PARTY_TYPE = decode(p_person_flag, 'Y', 'PERSON','ORGANIZATION')
AND status in ('A','I')
AND rownum = 1;
SELECT c.party_id
FROM hz_parties c
WHERE c.orig_system_reference = p_orig_system_contact_ref
AND c.request_id = p_request_id
AND NOT EXISTS
(SELECT 'X'
FROM hz_cust_accounts y
WHERE y.orig_system_reference = p_orig_system_contact_ref
AND y.party_id = c.party_id);
SELECT rel.party_id
FROM hz_relationships rel,
hz_org_contacts cont
WHERE cont.request_id = p_request_id
AND cont.orig_system_reference = p_orig_system_contact_ref
AND cont.party_relationship_id = rel.relationship_id
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES';
SELECT rel.relationship_id
FROM hz_relationships rel,
hz_org_contacts cont
WHERE cont.request_id = p_request_id
AND cont.orig_system_reference = p_orig_system_contact_ref
AND cont.party_relationship_id = rel.relationship_id
AND rel.subject_table_name = 'HZ_PARTIES'
AND rel.object_table_name = 'HZ_PARTIES';
select 'X'
into l_count
from hz_cust_accounts cust
,hz_cust_acct_sites site
,hz_cust_site_uses su
where site.orig_system_reference = p_bill_to_orig_address_ref
and site.cust_acct_site_id = su.cust_acct_site_id
and site.cust_account_id = cust.cust_account_id
and cust.orig_system_reference = p_orig_system_customer_ref
and su.site_use_code = 'BILL_TO'
and su.status = 'A'
and site.status = 'A';
select 'X'
into l_count
from ra_customers_interface i,
ra_customers_interface i1
where
i.request_id = req_id
and i.bill_to_orig_address_ref is not NULL
and i1.site_use_code = 'BILL_TO'
and i.bill_to_orig_address_ref = i1.orig_system_address_ref
and i.orig_system_address_ref = p_orig_system_address_ref
and i.orig_system_customer_ref = p_orig_system_customer_ref
and i.rowid <> i1.rowid
and i.interface_status is null
and rownum = 1;
SELECT 'x'
FROM hz_cust_accounts cust,
hz_cust_acct_sites_all site, -- bug 4454799
hz_cust_site_uses_all su -- bug 4454799
WHERE cust.orig_system_reference = p_orig_system_customer_ref
AND site.orig_system_reference = p_bill_to_orig_address_ref
AND site.org_id = p_org_id -- bug 4454799
AND site.cust_account_id = cust.cust_account_id
AND site.cust_acct_site_id = su.cust_acct_site_id
AND site.org_id = su.org_id -- bug 4454799
AND su.site_use_code = 'BILL_TO'
AND su.status = 'A'
AND site.status = 'A'
UNION ALL
SELECT 'x'
FROM hz_cust_accounts cust,
hz_cust_acct_sites_all site, -- bug 4454799
hz_cust_site_uses_all su -- bug 4454799
WHERE cust.orig_system_reference = p_orig_system_parent_ref
AND site.orig_system_reference = p_bill_to_orig_address_ref
AND site.org_id = p_org_id -- bug 4454799
AND site.cust_account_id = cust.cust_account_id
AND site.cust_acct_site_id = su.cust_acct_site_id
AND site.org_id = su.org_id -- bug 4454799
AND su.site_use_code = 'BILL_TO'
AND su.status = 'A'
AND site.status = 'A';
SELECT 'x'
FROM hz_cust_accounts cust,
hz_cust_acct_relate_all rel, -- bug 4454799
hz_cust_acct_sites_all site, -- bug 4454799
hz_cust_site_uses_all su -- bug 4454799
WHERE cust.orig_system_reference = p_orig_system_customer_ref
AND rel.related_cust_account_id = cust.cust_account_id
AND rel.bill_to_flag = 'Y'
AND site.cust_account_id = rel.cust_account_id
AND site.orig_system_reference = p_bill_to_orig_address_ref
AND site.org_id = p_org_id -- bug 4454799
AND site.org_id = su.org_id -- bug 4454799
AND site.org_id = rel.org_id -- bug 4454799
AND site.cust_acct_site_id = su.cust_acct_site_id
AND su.site_use_code = 'BILL_TO'
AND su.status = 'A'
AND site.status = 'A';
SELECT 'x'
FROM ra_customers_interface i,
ra_customers_interface_all i1 -- bug 4454799
WHERE i.request_id = req_id
AND i.bill_to_orig_address_ref is not NULL
AND i.orig_system_customer_ref = p_orig_system_customer_ref
AND i.orig_system_address_ref = p_orig_system_address_ref
AND i.bill_to_orig_address_ref = i1.orig_system_address_ref
AND i.org_id = p_org_id -- bug 4454799
AND i.org_id = i1.org_id -- bug 4454799
AND i1.site_use_code = 'BILL_TO'
AND i.rowid <> i1.rowid
AND i1.interface_status is null
AND rownum = 1
UNION ALL
SELECT 'x'
FROM ra_customers_interface i,
ra_customers_interface_all i1 -- bug 4454799
WHERE i.request_id = req_id
AND i.bill_to_orig_address_ref is not NULL
AND i.orig_system_customer_ref = p_orig_system_customer_ref
AND i1.orig_system_customer_ref = i.orig_system_parent_ref
AND i.orig_system_address_ref = p_orig_system_address_ref
AND i.org_id = p_org_id -- bug 4454799
AND i.org_id = i1.org_id -- bug 4454799
AND i.bill_to_orig_address_ref = i1.orig_system_address_ref
AND i1.site_use_code = 'BILL_TO'
AND i.rowid <> i1.rowid
AND i1.interface_status is null
AND rownum = 1;
SELECT orig_system_parent_ref, orig_system_party_ref
FROM ra_customers_interface
WHERE orig_system_customer_ref = p_orig_system_customer_ref
AND ROWNUM = 1;
FUNCTION validate_profile(v_insert_update_flag IN VARCHAR,
v_orig_system_customer_ref IN VARCHAR,
v_orig_system_address_ref IN VARCHAR,
v_org_id IN NUMBER,
v_request_id IN NUMBER)
RETURN VARCHAR2 AS
l_dummy VARCHAR(1);
CURSOR c1 IS -- Check if Cust record exists for ninsertion
SELECT 'x'
FROM ra_customers_interface
WHERE orig_system_customer_ref = v_orig_system_customer_ref
AND interface_status is null
AND request_id = v_request_id;
SELECT 'x'
FROM hz_customer_profiles p ,hz_cust_accounts c
WHERE c.orig_system_reference = v_orig_system_customer_ref
AND p.cust_account_id = c.cust_account_id
AND p.site_use_id is null;
SELECT 'x' -- The address ref should exist as Bill To and defined for the customer
FROM hz_cust_acct_sites_all ra, hz_cust_site_uses_all rsu, hz_cust_accounts rc -- bug 4454799
WHERE ra.orig_system_reference = v_orig_system_address_ref
AND ra.org_id = v_org_id -- bug 4454799
AND rc.orig_system_reference = v_orig_system_customer_ref
AND rc.cust_account_id = ra.cust_account_id
AND ra.cust_acct_site_id = rsu.cust_acct_site_id
AND ra.org_id = rsu.org_id -- bug 4454799
AND rsu.status = 'A'
AND rsu.site_use_code in ('BILL_TO','DUN','STMTS')
UNION ALL
SELECT 'x' -- If not already defined, THEN address rec should
-- exist in interface table with Bill To
FROM ra_customers_interface
WHERE orig_system_customer_ref = v_orig_system_customer_ref
AND interface_status is null
AND orig_system_address_ref = v_orig_system_address_ref
AND org_id = v_org_id -- bug 4454799
AND request_id = v_request_id
--Bug fix 2473275
AND site_use_code in ('BILL_TO','DUN','STMTS');
SELECT 'x'
FROM hz_customer_profiles p,
hz_cust_acct_sites_all ra, -- bug 4454799
hz_cust_site_uses_all rsu -- but 4454799
WHERE ra.orig_system_reference = v_orig_system_address_ref
AND ra.org_id = v_org_id -- bug 4454799
AND ra.org_id = rsu.org_id -- bug 4454799
AND ra.cust_acct_site_id = rsu.cust_acct_site_id
AND rsu.status = 'A'
--Bug fix 2473275
AND rsu.site_use_code in ('BILL_TO','DUN','STMTS')
AND rsu.site_use_id = p.site_use_id;
IF v_insert_update_flag NOT IN ('I','U') THEN
RETURN 'J8,';
IF v_insert_update_flag = 'I' THEN -- Insert New Profile
-- check if the customer ref is valid. if not, reject with status = S1
OPEN c1;
IF v_insert_update_flag = 'U' THEN -- Updating existing profile
-- This customer should have a profile defined already.
-- if not defined reject with status = 'a4'
OPEN c2;
IF v_insert_update_flag = 'I' THEN -- Insert New Profile
-- First check if the address has been already created
-- as a Bill_TO,DUNNING or STATEMENTS or if not created THEN should be in the
-- interface table with no error AND should be BILL_TO,DUNNING or STATEMENTS.
OPEN c3;
IF v_insert_update_flag = 'U' THEN -- Updating Existing Profile
-- This Site should have a profile defined already.
-- if not defined reject with status = 'a4'
OPEN c4;
SELECT 'X'
FROM ar_location_values v,
ar_location_values pv,
ar_location_values gv,
ar_location_values ggv,
ar_location_rates r
WHERE v.location_structure_id = p_location_structure_id
AND v.location_segment_id = r.location_segment_id
AND v.location_segment_value = UPPER(p_postal_code)
AND v.location_segment_qualifier = 'POSTAL_CODE'
AND TRUNC(p_creation_date)
BETWEEN TRUNC(r.start_date)
AND NVL(TRUNC(r.end_date), TRUNC(p_creation_date))
AND p_postal_code BETWEEN r.from_postal_code AND r.to_postal_code
AND v.parent_segment_id = pv.location_segment_id(+)
AND pv.parent_segment_id = gv.location_segment_id(+)
AND gv.parent_segment_id = ggv.location_segment_id(+)
AND (pv.location_segment_value = UPPER(p_city)
OR p_city IS NULL)
AND (gv.location_segment_value = UPPER(p_county)
OR p_county IS NULL)
AND (ggv.location_segment_value = UPPER(p_state)
OR p_state IS NULL );
SELECT 'X'
FROM ar_location_values v,
ar_location_values pv,
ar_location_values gv,
ar_location_rates r
WHERE v.location_structure_id = p_location_structure_id
AND v.location_segment_id = r.location_segment_id
AND v.location_segment_value = UPPER(p_city)
AND v.location_segment_qualifier = 'CITY'
AND TRUNC(p_creation_date)
BETWEEN TRUNC(r.start_date)
AND NVL(TRUNC(r.end_date), TRUNC(p_creation_date))
AND p_postal_code BETWEEN r.from_postal_code AND r.to_postal_code
AND v.parent_segment_id = pv.location_segment_id(+)
AND pv.parent_segment_id = gv.location_segment_id(+)
AND (pv.location_segment_value = UPPER(p_county)
OR p_county IS NULL)
AND (gv.location_segment_value = UPPER(p_state)
OR p_state IS NULL);
SELECT 'X'
FROM ar_location_values v,
ar_location_values gv,
ar_location_rates r
WHERE v.location_structure_id = p_location_structure_id
AND v.location_segment_id = r.location_segment_id
AND v.location_segment_value = UPPER(p_child_value)
AND v.location_segment_qualifier = p_child
AND TRUNC(p_creation_date)
BETWEEN TRUNC(r.start_date)
AND nvl(TRUNC(r.end_date), TRUNC(p_creation_date))
AND p_postal_code BETWEEN r.from_postal_code AND r.to_postal_code
AND v.parent_segment_id = gv.location_segment_id(+)
AND (gv.location_segment_value = UPPER(p_parent_value)
OR p_state IS NULL);
SELECT 'X'
FROM ar_location_values v, ar_location_rates r
WHERE v.location_structure_id = p_location_structure_id
AND v.location_segment_id = r.location_segment_id
AND v.location_segment_value = UPPER(p_value)
AND v.location_segment_qualifier = p_segment
AND TRUNC(p_creation_date)
BETWEEN TRUNC(r.start_date)
AND nvl(TRUNC(r.end_date), TRUNC(p_creation_date))
AND p_postal_code BETWEEN r.from_postal_code AND r.to_postal_code
;
select
rtrim(substr(l_struct,1,instr(l_struct,'.')),'.'),ltrim(substr(l_struct,instr(l_struct,'.')),'.') into l_child,l_parent
from dual;
SELECT decode(party.orig_system_reference,p_orig_system_party_ref,'','Y3,')
FROM hz_parties party
WHERE party.party_number = p_party_number;
SELECT decode(party.orig_system_reference,p_orig_system_customer_ref,'','Y3,')
FROM hz_parties party
WHERE party.party_number = p_party_number;
SELECT decode(NVL(i.orig_system_party_ref,i.orig_system_customer_ref),p_orig_system_party_ref,'','Y3,')
FROM ra_customers_interface_all i
WHERE i.party_number = p_party_number
AND i.request_id = req_id
AND i.rowid <> p_rowid ;
SELECT decode(NVL(i.orig_system_party_ref,i.orig_system_customer_ref),p_orig_system_customer_ref,'','Y3,')
FROM ra_customers_interface_all i
WHERE i.party_number = p_party_number
AND i.request_id = req_id
AND i.rowid <> p_rowid ;
SELECT decode(i.party_number,p_party_number,'','Y4,')
FROM ra_customers_interface_all i
WHERE i.orig_system_party_ref = p_orig_system_party_ref
AND i.request_id = req_id
AND i.rowid <> p_rowid ;
SELECT decode(i.party_number,p_party_number,'','Y4,')
FROM ra_customers_interface_all i
WHERE i.orig_system_customer_ref = p_orig_system_party_ref
AND i.orig_system_party_ref is null
AND i.request_id = req_id
AND i.rowid <> p_rowid ;
SELECT decode(i.party_number,p_party_number,'','Y4,')
FROM ra_customers_interface_all i
WHERE i.orig_system_party_ref = p_orig_system_customer_ref
AND i.request_id = req_id
AND i.rowid <> p_rowid ;
SELECT decode(i.party_number,p_party_number,'','Y4,')
FROM ra_customers_interface_all i
WHERE i.orig_system_customer_ref = p_orig_system_customer_ref
AND i.orig_system_party_ref is null
AND i.request_id = req_id
AND i.rowid <> p_rowid ;
SELECT decode(cust.orig_system_reference,p_orig_system_customer_ref,'','A5,')
FROM hz_cust_accounts cust
WHERE cust.account_number = p_customer_number;
SELECT decode(i.orig_system_customer_ref,p_orig_system_customer_ref,'','A5,')
FROM ra_customers_interface_all i
WHERE i.customer_number = p_customer_number
AND i.request_id = req_id
AND i.rowid <> p_rowid ;
SELECT decode(cust_site.orig_system_reference,p_orig_system_address_ref,'','Y6,')
FROM hz_party_sites site, hz_cust_acct_sites_all cust_site -- bug 4454799
WHERE site.party_site_number = p_party_site_number
AND cust_site.org_id = p_org_id -- bug 4454799
AND site.party_site_id = cust_site.party_site_id;
SELECT decode(i.orig_system_address_ref,p_orig_system_address_ref,'','Y6,')
FROM ra_customers_interface_all i
WHERE i.party_site_number = p_party_site_number
AND i.org_id = p_org_id -- bug 4454799
AND i.request_id = req_id
AND i.rowid <> p_rowid ;
SELECT loc.location_id,loc.country,loc.city, loc.state,
loc.county, loc.province, loc.postal_code
FROM hz_cust_acct_sites_all cs, -- bug 4454799
hz_party_sites ps,
hz_locations loc
WHERE cs.orig_system_reference = p_orig_system_address_ref
AND cs.org_id = p_org_id -- bug 4454799
AND ps.party_site_id = cs.party_site_id
AND ps.location_id = loc.location_id;
SELECT 'Y'
INTO l_loc_assignment_exist
FROM DUAL
WHERE EXISTS (SELECT 1
FROM hz_loc_assignments la
WHERE la.location_id = l_location_id
);
SELECT 'Y'
INTO l_is_remit_to_location
FROM DUAL
WHERE EXISTS (SELECT 1
FROM hz_party_sites ps
WHERE ps.location_id = l_location_id
AND ps.party_id = -1
);
g_last_update_login := hz_utility_v2pub.last_update_login;
g_last_updated_by := hz_utility_v2pub.last_updated_by;
| update_exception_table
|
| DESCRIPTION
| Update win source exception table when mix-n-match is seted up.
|
| SCOPE - PRIVATE
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_i_party_id
| p_entity_attr_id
| p_value
| p_ue_ranking
| p_sst_is_null
| OUT:
| IN/ OUT:
| NOTES
|
| MODIFICATION HISTORY
|
| Jianying Huang 07/12/2002 Bug No : 2460837. Created.
|
+===========================================================================*/
PROCEDURE update_exception_table (
p_i_party_id IN t_id,
p_entity_attr_id IN NUMBER,
p_value IN t_varchar500,
p_ue_ranking IN NUMBER,
p_sst_is_null IN t_flag
) IS
i_party_id t_id := t_id();
DELETE hz_win_source_exceps
WHERE party_id = i_party_id(i)
AND entity_attr_id = p_entity_attr_id;
UPDATE hz_win_source_exceps exp
SET content_source_type = 'USER_ENTERED',
exception_type = (
SELECT DECODE(sign(s.ranking-p_ue_ranking), 0, exp.exception_type,
1, 'Migration', -1, 'Exception')
FROM hz_select_data_sources s
WHERE entity_attr_id = p_entity_attr_id
AND content_source_type = exp.content_source_type),
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login,
last_update_date = SYSDATE,
request_id = g_request_id,
program_application_id = g_program_application_id,
program_id = g_program_id,
program_update_date = SYSDATE
WHERE party_id = i_party_id(i)
AND entity_attr_id = p_entity_attr_id;
INSERT INTO hz_win_source_exceps (
party_id,
entity_attr_id,
content_source_type,
exception_type,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by,
request_id,
program_application_id,
program_id,
program_update_date
) SELECT
i_party_id(i),
p_entity_attr_id,
'USER_ENTERED',
decode(i_flag(i), '', 'Migration', 'Exception'),
g_created_by,
SYSDATE,
g_last_update_login,
SYSDATE,
g_last_updated_by,
g_request_id,
g_program_application_id,
g_program_id,
SYSDATE
FROM dual
WHERE NOT EXISTS (
SELECT 'Y'
FROM hz_win_source_exceps
WHERE party_id = i_party_id(i)
AND entity_attr_id = p_entity_attr_id );
END update_exception_table;
| update_org_ue_profile
|
| DESCRIPTION
| The procedure will be called in racudc.lpc to sync. user-entered profile
| and sst profile when mix-n-match is seted up.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_request_id
| OUT:
| IN/ OUT:
| NOTES
|
| MODIFICATION HISTORY
|
| Jianying Huang 07/12/2002 Bug No : 2460837. Created.
| Sisir 05/07/2003 Bug No : 2970763;Before create/update
| insert/update clause.
+===========================================================================*/
PROCEDURE update_org_ue_profile (
p_request_id IN NUMBER
) IS
-- The cursor is used to select interface related value for user-entered
-- profile.
CURSOR c_entity IS
SELECT /* decode(trunc(org.effective_start_date),trunc(sysdate),'U','C') create_update_flag,*/
decode(fnd_profile.value ('HZ_PROFILE_VERSION'),'NEW_VERSION','C','NO_VERSION','U',
decode(trunc(org.effective_start_date),trunc(sysdate),'U','C')) create_update_flag,
org.organization_profile_id,
org.party_id,
-- User NVL for bug 1404725. We do not need NVL on
-- customer name because it is a not-null column.
-- However, for some reason we did not do NVL on
-- customer name phonetic. Please see racudc.lpc.
-- If we decide to do NVL on phoneic also, we need
-- to modify both racudc.lpc and this procedure.
-- By selecting non-NVL value here is to differentiate
-- when customer is updating the column by passing
-- value and when he/she does not want to update the
-- column by setting the column to null. This information
-- will be used when update data source exception table.
nvl(i.jgzz_fiscal_code, org.jgzz_fiscal_code),
i.jgzz_fiscal_code,
decode(sst.jgzz_fiscal_code, '', 'Y', 'N'),
i.customer_name,
decode(sst.organization_name, '', 'Y', 'N'),
i.customer_name_phonetic,
decode(sst.organization_name_phonetic, '', 'Y', 'N'),
nvl(i.cust_tax_reference, org.tax_reference),
i.cust_tax_reference,
decode(sst.tax_reference, '', 'Y', 'N'),nvl(org.version_number,1)+1
FROM hz_organization_profiles org,
hz_organization_profiles sst,
ra_customers_interface_all i, -- Bug 4956131
hz_cust_accounts cust,
(SELECT min(i1.rowid) myrowid
FROM ra_customers_interface_all i1 -- Bug 4956131
WHERE i1.request_id = p_request_id
AND i1.interface_status IS NULL
AND i1.insert_update_flag='U'
AND NVL(i1.person_flag,'N') = 'N'
GROUP BY i1.orig_system_customer_ref) temp
WHERE i.rowid = temp.myrowid
AND i.request_id = p_request_id
AND i.orig_system_customer_ref = cust.orig_system_reference
AND cust.party_id = org.party_id
AND org.effective_end_date is null
AND org.actual_content_source = 'USER_ENTERED'
AND sst.party_id = org.party_id
AND sst.effective_end_date is null
AND sst.actual_content_source = 'SST'
ORDER BY create_update_flag;
i_create_update_flag t_flag;
SELECT s.entity_attr_id, s.ranking
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.attribute_name = UPPER(p_attribute_name)
AND e.entity_attr_id = s.entity_attr_id
AND s.content_source_type = 'USER_ENTERED';
update_start NUMBER := 0;
update_end NUMBER := 0;
i_create_update_flag,
i_ue_profile_id,
i_party_id,
i_jgzz_fiscal_code,
i1_jgzz_fiscal_code,
ss_jgzz_fiscal_code,
i_organization_name,
ss_organization_name,
i_organization_name_phonetic,
ss_organization_name_phonetic,
i_tax_reference,
i1_tax_reference,
ss_tax_reference,
i_version_number LIMIT rows;
update_start := 0; update_end := -1;
IF i_create_update_flag(i) = 'C' THEN
IF create_start = 0 THEN create_start := i; END IF;
IF update_start = 0 THEN
update_start := i;
IF update_start > 0 AND update_end = -1 THEN update_end := subtotal; END IF;
UPDATE hz_organization_profiles
SET effective_end_date = decode(trunc(effective_start_date),trunc(sysdate),trunc(sysdate),TRUNC(SYSDATE-1))
WHERE organization_profile_id = i_ue_profile_id(i);
INSERT INTO hz_organization_profiles (
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by,
request_id,
program_application_id,
program_id,
program_update_date,
content_source_type,
actual_content_source,
created_by_module,
application_id,
organization_profile_id,
party_id,
effective_start_date,
object_version_number,
jgzz_fiscal_code,
organization_name,
organization_name_phonetic,
tax_reference,
version_number
) VALUES (
g_created_by,
SYSDATE,
g_last_update_login,
SYSDATE,
g_last_updated_by,
g_request_id,
g_program_application_id,
g_program_id,
SYSDATE,
'USER_ENTERED',
'USER_ENTERED',
'TCA-CUSTOMER-INTERFACE',
222,
hz_organization_profiles_s.nextval,
i_party_id(i),
SYSDATE,
1,
i_jgzz_fiscal_code(i),
i_organization_name(i),
i_organization_name_phonetic(i),
i_tax_reference(i),
i_version_number(i)
);
FORALL i IN update_start..update_end
UPDATE hz_organization_profiles
SET
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login,
last_update_date = SYSDATE,
request_id = g_request_id,
program_application_id = g_program_application_id,
program_id = g_program_id,
program_update_date = SYSDATE,
jgzz_fiscal_code = i_jgzz_fiscal_code(i),
organization_name = i_organization_name(i),
organization_name_phonetic = i_organization_name_phonetic(i),
tax_reference = i_tax_reference(i),
version_number = nvl(version_number,1)+1
WHERE organization_profile_id = i_ue_profile_id(i);
update_exception_table(i_party_id,id_jgzz_fiscal_code,i1_jgzz_fiscal_code,rk_jgzz_fiscal_code,ss_jgzz_fiscal_code);
update_exception_table(i_party_id,id_organization_name,i_organization_name,rk_organization_name,ss_organization_name);
update_exception_table(i_party_id,id_organization_name_phonetic,i_organization_name_phonetic,rk_organization_name_phonetic,ss_organization_name_phonetic);
update_exception_table(i_party_id,id_tax_reference,i1_tax_reference,rk_tax_reference,ss_tax_reference);
END update_org_ue_profile;
| update_per_ue_profile
|
| DESCRIPTION
| The procedure will be called in racudc.lpc to sync. user-entered profile
| and sst profile when mix-n-match is seted up.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
|
| ARGUMENTS : IN:
| p_request_id
| OUT:
| IN/ OUT:
| NOTES
|
| MODIFICATION HISTORY
|
| Jianying Huang 07/12/2002 Bug No : 2460837. Created.
| Sisir 06/09/2003 Bug No : 2970763 Added additional
| clause of hz_profile_version in decode
| stmt and also added version_number
| column in the select clause.
+===========================================================================*/
PROCEDURE update_per_ue_profile (
p_request_id IN NUMBER
) IS
-- The cursor is used to select interface related value for user-entered
-- profile.
CURSOR c_entity IS
SELECT /* decode(trunc(per.effective_start_date),trunc(sysdate),'U','C') create_update_flag, */
decode(fnd_profile.value ('HZ_PROFILE_VERSION'),'NEW_VERSION','C','NO_VERSION','U',
decode(trunc(per.effective_start_date),trunc(sysdate),'U','C')) create_update_flag,
per.person_profile_id,
per.party_id,
-- User NVL for bug 1404725 when we have to take
-- value from hz_person_profiles. By selecting
-- non-NVL value here is to differentiate when
-- customer is updating the column by passing value
-- and when he/she does not want to update the column
-- by setting the column to null. This information
-- will be used when update data source exception table.
nvl(i.jgzz_fiscal_code, per.jgzz_fiscal_code),
i.jgzz_fiscal_code,
decode(sst.jgzz_fiscal_code, '', 'Y', 'N'),
i.customer_name,
decode(sst.person_name, '', 'Y', 'N'),
decode(i.person_first_name,'',decode(i.person_last_name,'',substrb(i.customer_name,1,150),''),i.person_first_name),
decode(sst.person_first_name, '', 'Y', 'N'),
nvl(i.person_last_name, per.person_last_name),
i.person_last_name,
decode(sst.person_last_name, '', 'Y', 'N'),
nvl(i.customer_name_phonetic, per.person_name_phonetic),
i.customer_name_phonetic,
decode(sst.person_name_phonetic, '', 'Y', 'N'),
decode(nvl(i.person_flag,'N'),'Y',i.customer_name_phonetic,''),
decode(sst.person_first_name_phonetic, '', 'Y', 'N'),
decode(nvl(i.person_flag,'N'),'Y',i.customer_name_phonetic,''),
decode(sst.person_last_name_phonetic, '', 'Y', 'N'),
nvl(i.cust_tax_reference, per.tax_reference),
i.cust_tax_reference,
decode(sst.tax_reference, '', 'Y', 'N'),nvl(per.version_number,1)+1
FROM hz_person_profiles per,
hz_person_profiles sst,
ra_customers_interface_all i, -- Bug 4956131
hz_cust_accounts cust,
(SELECT min(i1.rowid) myrowid
FROM ra_customers_interface_all i1 -- Bug 4956131
WHERE i1.request_id = p_request_id
AND i1.interface_status IS NULL
AND i1.insert_update_flag='U'
AND i1.person_flag = 'Y'
GROUP BY i1.orig_system_customer_ref) temp
WHERE i.rowid = temp.myrowid
AND i.request_id = p_request_id
AND i.orig_system_customer_ref = cust.orig_system_reference
AND cust.party_id = per.party_id
AND per.effective_end_date is null
AND per.actual_content_source = 'USER_ENTERED'
AND sst.party_id = per.party_id
AND sst.effective_end_date is null
AND sst.actual_content_source = 'SST'
ORDER BY create_update_flag;
i_create_update_flag t_flag;
SELECT s.entity_attr_id, s.ranking
FROM hz_entity_attributes e,
hz_select_data_sources s
WHERE e.attribute_name = UPPER(p_attribute_name)
AND e.entity_attr_id = s.entity_attr_id
AND s.content_source_type = 'USER_ENTERED';
update_start NUMBER := 0;
update_end NUMBER := 0;
i_create_update_flag,
i_ue_profile_id,
i_party_id,
i_jgzz_fiscal_code,
i1_jgzz_fiscal_code,
ss_jgzz_fiscal_code,
i_person_name,
ss_person_name,
i_person_first_name,
ss_person_first_name,
i_person_last_name,
i1_person_last_name,
ss_person_last_name,
i_person_name_phonetic,
i1_person_name_phonetic,
ss_person_name_phonetic,
i_person_first_name_phonetic,
ss_person_first_name_phonetic,
i_person_last_name_phonetic,
ss_person_last_name_phonetic,
i_tax_reference,
i1_tax_reference,
ss_tax_reference,
i_version_number LIMIT rows;
update_start := 0; update_end := -1;
IF i_create_update_flag(i) = 'C' THEN
IF create_start = 0 THEN create_start := i; END IF;
IF update_start = 0 THEN
update_start := i;
IF update_start > 0 AND update_end = -1 THEN update_end := subtotal; END IF;
UPDATE hz_person_profiles
SET effective_end_date = decode(trunc(effective_start_date),trunc(sysdate),trunc(sysdate),TRUNC(SYSDATE-1))
WHERE person_profile_id = i_ue_profile_id(i);
INSERT INTO hz_person_profiles (
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by,
request_id,
program_application_id,
program_id,
program_update_date,
content_source_type,
actual_content_source,
created_by_module,
application_id,
person_profile_id,
party_id,
effective_start_date,
object_version_number,
jgzz_fiscal_code,
person_name,
person_first_name,
person_last_name,
person_name_phonetic,
person_first_name_phonetic,
person_last_name_phonetic,
tax_reference,
version_number
) VALUES (
g_created_by,
SYSDATE,
g_last_update_login,
SYSDATE,
g_last_updated_by,
g_request_id,
g_program_application_id,
g_program_id,
SYSDATE,
'USER_ENTERED',
'USER_ENTERED',
'TCA-CUSTOMER-INTERFACE',
222,
hz_person_profiles_s.nextval,
i_party_id(i),
SYSDATE,
1,
i_jgzz_fiscal_code(i),
i_person_name(i),
i_person_first_name(i),
i_person_last_name(i),
i_person_name_phonetic(i),
i_person_first_name_phonetic(i),
i_person_last_name_phonetic(i),
i_tax_reference(i),
i_version_number(i)
);
FORALL i IN update_start..update_end
UPDATE hz_person_profiles
SET
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login,
last_update_date = SYSDATE,
request_id = g_request_id,
program_application_id = g_program_application_id,
program_id = g_program_id,
program_update_date = SYSDATE,
jgzz_fiscal_code = i_jgzz_fiscal_code(i),
person_name = i_person_name(i),
person_first_name = i_person_first_name(i),
person_last_name = i_person_last_name(i),
person_name_phonetic = i_person_name_phonetic(i),
person_first_name_phonetic = i_person_first_name_phonetic(i),
person_last_name_phonetic = i_person_last_name_phonetic(i),
tax_reference = i_tax_reference(i),
version_number = nvl(version_number,1)+1
WHERE person_profile_id = i_ue_profile_id(i);
update_exception_table(i_party_id,id_jgzz_fiscal_code,i1_jgzz_fiscal_code,rk_jgzz_fiscal_code,ss_jgzz_fiscal_code);
update_exception_table(i_party_id,id_person_name,i_person_name,rk_person_name,ss_person_name);
update_exception_table(i_party_id,id_person_first_name,i_person_first_name,rk_person_first_name,ss_person_first_name);
update_exception_table(i_party_id,id_person_last_name,i1_person_last_name,rk_person_last_name,ss_person_last_name);
update_exception_table(i_party_id,id_person_name_phonetic,i1_person_name_phonetic,rk_person_name_phonetic,ss_person_name_phonetic);
update_exception_table(i_party_id,id_person_first_name_phonetic,i_person_first_name_phonetic,rk_person_first_name_phonetic,ss_person_first_name_phonetic);
update_exception_table(i_party_id,id_person_last_name_phonetic,i_person_last_name_phonetic,rk_person_last_name_phonetic,ss_person_last_name_phonetic);
update_exception_table(i_party_id,id_tax_reference,i1_tax_reference,rk_tax_reference,ss_tax_reference);
END update_per_ue_profile;
SELECT su.primary_flag
FROM hz_cust_accounts cust,
hz_cust_acct_sites_all site, -- bug 4454799
hz_cust_site_uses_all su -- bug 4454799
WHERE cust.orig_system_reference = p_orig_system_customer_ref
and cust.cust_account_id = site.cust_account_id
and site.cust_acct_site_id = su.cust_acct_site_id
and site.org_id = p_org_id -- bug 4454799
and site.org_id = su.org_id -- bug 4454799
and su.site_use_code in ('STMTS','DUN','LEGAL')
and su.site_use_code = p_site_use_code
and su.status = 'A'
and rownum = 1;
SELECT site.cust_acct_site_id,suse.site_use_id
FROM hz_cust_accounts cust,
hz_cust_acct_sites_all site,
hz_cust_site_uses_all suse
WHERE cust.orig_system_reference = p_orig_system_customer_ref
and cust.cust_account_id = site.cust_account_id
and site.cust_acct_site_id = suse.cust_acct_site_id
and site.org_id = p_org_id -- bug 4454799
and site.org_id = suse.org_id -- bug 4454799
and suse.site_use_code = p_site_use_code
and suse.status = 'A'
and suse.primary_flag = 'Y'
and rownum = 1;
update hz_cust_site_uses_all
set primary_flag = 'N',
last_update_login = hz_utility_v2pub.last_update_login,
last_update_date = SYSDATE,
last_updated_by = hz_utility_v2pub.last_updated_by,
request_id = hz_utility_v2pub.request_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id
where site_use_id = l_site_use_id;
update hz_cust_acct_sites_all
set BILL_TO_FLAG = decode(p_site_use_code,'BILL_TO','Y',BILL_TO_FLAG),
SHIP_TO_FLAG = decode(p_site_use_code,'SHIP_TO','Y',SHIP_TO_FLAG),
MARKET_FLAG = decode(p_site_use_code,'MARKET','Y',MARKET_FLAG),
last_update_login = hz_utility_v2pub.last_update_login,
last_update_date = SYSDATE,
last_updated_by = hz_utility_v2pub.last_updated_by,
request_id = hz_utility_v2pub.request_id,
program_application_id = hz_utility_v2pub.program_application_id,
program_id = hz_utility_v2pub.program_id
where cust_acct_site_id = l_acct_site_id;
(SELECT 'THIRD_PARTY' PARTY_TYPE_CODE,
party.party_id PARTY_ID,
party.country COUNTRY_CODE, --4742586
FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
party.tax_reference TAX_REFERENCE,
SYSDATE CREATION_DATE,
FND_GLOBAL.User_ID CREATED_BY,
SYSDATE LAST_UPDATE_DATE,
FND_GLOBAL.User_ID LAST_UPDATED_BY,
FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
FROM HZ_PARTIES party, ra_customers_interface_all rci -- Bug 4956131
WHERE party.orig_system_reference = nvl(rci.orig_system_party_ref, rci.orig_system_customer_ref)
AND party.request_id = p_request_id
AND rci.interface_status is null
AND rci.request_id = p_request_id
AND rci.insert_update_flag = 'I'
AND (rci.rowid = ( SELECT min(i2.rowid)
FROM ra_customers_interface_all i2 -- Bug 4956131
WHERE i2.orig_system_customer_ref = rci.orig_system_customer_ref
AND rci.orig_system_party_ref is null
AND i2.interface_status is null
AND i2.request_id = p_request_id
AND i2.insert_update_flag = 'I') OR
rci.rowid = ( SELECT min(i2.rowid)
FROM ra_customers_interface_all i2 -- Bug 4956131
WHERE i2.orig_system_party_ref = rci.orig_system_party_ref
AND i2.interface_status is null
AND i2.request_id = p_request_id
AND i2.insert_update_flag = 'I'))
AND (party.party_type ='ORGANIZATION' OR party.party_type ='PERSON')) PTY
ON (PTY.PARTY_ID = PTP.PARTY_ID AND PTP.PARTY_TYPE_CODE = 'THIRD_PARTY')
WHEN MATCHED THEN
UPDATE SET
PTP.REP_REGISTRATION_NUMBER = PTY.TAX_REFERENCE,
PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
PTP.PROGRAM_ID = hz_utility_v2pub.program_id,
PTP.PROGRAM_APPLICATION_ID = hz_utility_v2pub.program_application_id,
PTP.REQUEST_ID = p_request_id,
PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
WHEN NOT MATCHED THEN
INSERT (PARTY_TYPE_CODE,
PARTY_TAX_PROFILE_ID,
PARTY_ID,
PROGRAM_LOGIN_ID,
REP_REGISTRATION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
OBJECT_VERSION_NUMBER,
COUNTRY_CODE)--4742586
VALUES (PTY.PARTY_TYPE_CODE,
ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
PTY.PARTY_ID,
PTY.PROGRAM_LOGIN_ID,
PTY.TAX_REFERENCE,
PTY.CREATION_DATE,
PTY.CREATED_BY,
PTY.LAST_UPDATE_DATE,
PTY.LAST_UPDATED_BY,
PTY.LAST_UPDATE_LOGIN,
hz_utility_v2pub.program_id,
hz_utility_v2pub.program_application_id,
p_request_id,
1,
PTY.COUNTRY_CODE );--4742586
(SELECT 'THIRD_PARTY' PARTY_TYPE_CODE,
party.party_id PARTY_ID,
party.country COUNTRY_CODE,
FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
party.tax_reference TAX_REFERENCE,
SYSDATE CREATION_DATE,
FND_GLOBAL.User_ID CREATED_BY,
SYSDATE LAST_UPDATE_DATE,
FND_GLOBAL.User_ID LAST_UPDATED_BY,
FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
FROM HZ_PARTIES party, RA_CONTACT_PHONES_INT_ALL rcpi
WHERE party.orig_system_reference = rcpi.orig_system_contact_ref
AND party.request_id = p_request_id
AND rcpi.interface_status is null
AND rcpi.request_id = p_request_id
AND rcpi.insert_update_flag = 'I'
AND rcpi.rowid = ( SELECT min(i2.rowid)
FROM RA_CONTACT_PHONES_INT_ALL i2
WHERE i2.orig_system_contact_ref = rcpi.orig_system_contact_ref
AND i2.interface_status is null
AND i2.request_id = p_request_id
AND i2.insert_update_flag = 'I')
AND party.party_type ='PERSON') PTY
ON (PTY.PARTY_ID = PTP.PARTY_ID AND PTP.PARTY_TYPE_CODE = 'THIRD_PARTY')
WHEN MATCHED THEN
UPDATE SET
PTP.REP_REGISTRATION_NUMBER = PTY.TAX_REFERENCE,
PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
PTP.PROGRAM_ID = hz_utility_v2pub.program_id,
PTP.PROGRAM_APPLICATION_ID = hz_utility_v2pub.program_application_id,
PTP.REQUEST_ID = p_request_id,
PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
WHEN NOT MATCHED THEN
INSERT (PARTY_TYPE_CODE,
PARTY_TAX_PROFILE_ID,
PARTY_ID,
PROGRAM_LOGIN_ID,
REP_REGISTRATION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
OBJECT_VERSION_NUMBER,
COUNTRY_CODE)
VALUES (PTY.PARTY_TYPE_CODE,
ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
PTY.PARTY_ID,
PTY.PROGRAM_LOGIN_ID,
PTY.TAX_REFERENCE,
PTY.CREATION_DATE,
PTY.CREATED_BY,
PTY.LAST_UPDATE_DATE,
PTY.LAST_UPDATED_BY,
PTY.LAST_UPDATE_LOGIN,
hz_utility_v2pub.program_id,
hz_utility_v2pub.program_application_id,
p_request_id,
1,
PTY.COUNTRY_CODE );
(SELECT 'THIRD_PARTY_SITE' PARTY_TYPE_CODE,
ps.party_site_id PARTY_ID,
loc.country COUNTRY_CODE,--4742586
FND_GLOBAL.Login_ID PROGRAM_LOGIN_ID ,
NULL TAX_REFERENCE,
SYSDATE CREATION_DATE,
FND_GLOBAL.User_ID CREATED_BY,
SYSDATE LAST_UPDATE_DATE,
FND_GLOBAL.User_ID LAST_UPDATED_BY,
FND_GLOBAL.Login_ID LAST_UPDATE_LOGIN
FROM HZ_PARTY_SITES ps, ra_customers_interface_all rci, -- Bug 4956131
HZ_LOCATIONS loc --4742586
WHERE ps.orig_system_reference = rci.orig_system_address_ref
AND loc.location_id = ps.location_id --4742586
AND ps.request_id = p_request_id
AND rci.interface_status is null
AND rci.request_id = p_request_id
AND rci.insert_update_flag = 'I'
AND (rci.rowid = ( SELECT min(i2.rowid)
FROM ra_customers_interface_all i2 -- Bug 4956131
WHERE i2.orig_system_address_ref = rci.orig_system_address_ref
AND i2.interface_status is null
AND i2.request_id = p_request_id
AND i2.insert_update_flag = 'I'))) PTY
ON (PTY.PARTY_ID = PTP.PARTY_ID AND PTP.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE')
WHEN MATCHED THEN
UPDATE SET
PTP.LAST_UPDATE_DATE=PTY.LAST_UPDATE_DATE,
PTP.LAST_UPDATED_BY=PTY.LAST_UPDATED_BY,
PTP.LAST_UPDATE_LOGIN=PTY.LAST_UPDATE_LOGIN,
PTP.PROGRAM_ID = hz_utility_v2pub.program_id,
PTP.PROGRAM_APPLICATION_ID = hz_utility_v2pub.program_application_id,
PTP.REQUEST_ID = p_request_id,
PTP.OBJECT_VERSION_NUMBER = PTP.OBJECT_VERSION_NUMBER +1
WHEN NOT MATCHED THEN
INSERT (
PARTY_TYPE_CODE,
PARTY_TAX_PROFILE_ID,
PARTY_ID,
PROGRAM_LOGIN_ID,
REP_REGISTRATION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
OBJECT_VERSION_NUMBER,
COUNTRY_CODE)--4742586
VALUES (
PTY.PARTY_TYPE_CODE,
ZX_PARTY_TAX_PROFILE_S.NEXTVAL,
PTY.PARTY_ID,
PTY.PROGRAM_LOGIN_ID,
PTY.TAX_REFERENCE,
PTY.CREATION_DATE,
PTY.CREATED_BY,
PTY.LAST_UPDATE_DATE,
PTY.LAST_UPDATED_BY,
PTY.LAST_UPDATE_LOGIN,
hz_utility_v2pub.program_id,
hz_utility_v2pub.program_application_id,
p_request_id,
1,
PTY.COUNTRY_CODE);--4742586
PROCEDURE insert_ci_party_usages
(
p_request_id IN NUMBER
)
IS
BEGIN
INSERT INTO hz_party_usg_assignments(
PARTY_USG_ASSIGNMENT_ID
,PARTY_ID
,PARTY_USAGE_CODE
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,STATUS_FLAG
,COMMENTS
,OWNER_TABLE_NAME
,OWNER_TABLE_ID
,OBJECT_VERSION_NUMBER
,CREATED_BY_MODULE
,APPLICATION_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID )
SELECT hz_party_usg_assignments_s.nextval -- PARTY_USG_ASSIGNMENT_ID
,hzp.party_id -- PARTY_ID
,'CUSTOMER' -- PARTY_USAGE_CODE
,trunc(SYSDATE) -- EFFECTIVE_START_DATE
,decode((select min(status)
from hz_cust_accounts
where party_id = hzp.party_id),
'A',to_date('31-12-4712','DD-MM-YYYY')
,trunc(SYSDATE)) -- EFFECTIVE_END_DATE
,(select min(status)
from hz_cust_accounts
where party_id = hzp.party_id) -- STATUS_FLAG
,'' -- COMMENTS
,'' -- OWNER_TABLE_NAME
,'' -- OWNER_TABLE_ID
,1 -- OBJECT_VERSION_NUMBER
,'CUST_INTERFACE' -- CREATED_BY_MODULE
,'' -- APPLICATION_ID
,hz_utility_v2pub.created_by -- CREATED_BY
, SYSDATE -- CREATION_DATE
,hz_utility_v2pub.last_update_login -- LAST_UPDATE_LOGIN
, SYSDATE -- LAST_UPDATE_DATE
,hz_utility_v2pub.last_updated_by -- LAST_UPDATED_BY
,p_request_id -- REQUEST_ID
,hz_utility_v2pub.program_application_id -- PROGRAM_APPLICATION_ID
,hz_utility_v2pub.program_id -- PROGRAM_ID
from ra_customers_interface rci,
hz_parties hzp
WHERE hzp.orig_system_reference = nvl(rci.orig_system_party_ref, rci.orig_system_customer_ref)
and hzp.request_id = p_request_id
AND rci.interface_status is null
AND rci.insert_update_flag = 'I'
AND ( rci.rowid = (SELECT min(i2.rowid)
FROM ra_customers_interface i2
WHERE i2.orig_system_customer_ref =
rci.orig_system_customer_ref
and rci.orig_system_party_ref is null
AND i2.interface_status is null
AND i2.insert_update_flag = 'I') OR
rci.rowid = (SELECT min(i2.rowid)
FROM ra_customers_interface i2
WHERE i2.orig_system_party_ref = rci.orig_system_party_ref
AND i2.interface_status is null
AND i2.insert_update_flag = 'I')
);
END insert_ci_party_usages;
PROCEDURE insert_nci_party_usages
(
p_request_id IN NUMBER
)
IS
BEGIN
INSERT INTO hz_party_usg_assignments(
PARTY_USG_ASSIGNMENT_ID
,PARTY_ID
,PARTY_USAGE_CODE
,EFFECTIVE_START_DATE
,EFFECTIVE_END_DATE
,STATUS_FLAG
,COMMENTS
,OWNER_TABLE_NAME
,OWNER_TABLE_ID
,OBJECT_VERSION_NUMBER
,CREATED_BY_MODULE
,APPLICATION_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID )
SELECT hz_party_usg_assignments_s.nextval -- PARTY_USG_ASSIGNMENT_ID
,hzp.party_id -- PARTY_ID
,'CUSTOMER' -- PARTY_USAGE_CODE
,trunc(SYSDATE) -- EFFECTIVE_START_DATE
,decode((select min(status)
from hz_cust_accounts
where party_id = hzp.party_id),
'A',to_date('31-12-4712','DD-MM-YYYY')
,trunc(SYSDATE)) -- EFFECTIVE_END_DATE
,(select min(status)
from hz_cust_accounts
where party_id = hzp.party_id)-- STATUS_FLAG
,'' -- COMMENTS
,'' -- OWNER_TABLE_NAME
,'' -- OWNER_TABLE_ID
,1 -- OBJECT_VERSION_NUMBER
,'CUST_INTERFACE' -- CREATED_BY_MODULE
,'' -- APPLICATION_ID
,hz_utility_v2pub.created_by -- CREATED_BY
, SYSDATE -- CREATION_DATE
,hz_utility_v2pub.last_update_login -- LAST_UPDATE_LOGIN
, SYSDATE -- LAST_UPDATE_DATE
,hz_utility_v2pub.last_updated_by -- LAST_UPDATED_BY
,p_request_id -- REQUEST_ID
,hz_utility_v2pub.program_application_id-- PROGRAM_APPLICATION_ID
,hz_utility_v2pub.program_id -- PROGRAM_ID
from ra_customers_interface_all rci, -- Bug 4956131
hz_parties hzp
WHERE hzp.party_id = HZ_CUSTOMER_INT.get_account_party_id(rci.orig_system_party_ref,rci.person_flag,'P')
AND rci.request_id = p_request_id
AND rci.interface_status is null
AND rci.insert_update_flag = 'I'
AND ( rci.rowid = (SELECT min(i2.rowid)
FROM ra_customers_interface_all i2 -- Bug 4956131
WHERE i2.orig_system_customer_ref =
rci.orig_system_customer_ref
and rci.orig_system_party_ref is null
AND i2.interface_status is null
AND i2.request_id = p_request_id
AND i2.insert_update_flag = 'I') OR
rci.rowid = (SELECT min(i2.rowid)
FROM ra_customers_interface_all i2 -- Bug 4956131
WHERE i2.orig_system_party_ref = rci.orig_system_party_ref
AND i2.request_id = p_request_id
AND i2.interface_status is null
AND i2.insert_update_flag = 'I')
)
and not exists( SELECT '1'
FROM hz_parties
WHERE party_id = hzp.party_id
AND request_id = p_request_id )
and not exists(
select '1'
from hz_party_usg_assignments pua
where pua.party_id = hzp.party_id
and party_usage_code = 'CUSTOMER'
and pua.status_flag = ( select min(status)
from hz_cust_accounts
where party_id = hzp.party_id)
and pua.effective_start_date <= decode((select min(status)
from hz_cust_accounts
where party_id = hzp.party_id),
'A',trunc(SYSDATE)
,pua.effective_start_date)
and nvl(pua.effective_end_date,to_date('31-12-4712','DD-MM-YYYY')) >= decode((select min(status)
from hz_cust_accounts
where party_id = hzp.party_id),
'A',trunc(SYSDATE)
,nvl(pua.effective_start_date,
to_date('31-12-4712','DD-MM-YYYY')
)));
END insert_nci_party_usages;
CURSOR c_tax_reference IS SELECT party_id, rep_registration_number
FROM zx_party_tax_profile
WHERE request_id = p_request_id
AND party_type_code ='THIRD_PARTY';
UPDATE HZ_PARTIES p
SET tax_reference = l_tax_reference(i)
WHERE p.party_id = l_party_id(i);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of records updated in HZ_PARTIES : ' || l_party_id.count);
UPDATE HZ_PERSON_PROFILES per
SET tax_reference = l_tax_reference(i)
WHERE per.party_id = l_party_id(i)
AND actual_content_source = 'SST'
AND effective_end_date IS NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of records updated in HZ_PERSON_PROFILES : ' || l_party_id.count);
UPDATE HZ_ORGANIZATION_PROFILES org
SET tax_reference = l_tax_reference(i)
WHERE party_id = l_party_id(i)
AND actual_content_source = 'SST'
AND effective_end_date IS NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of records updated in HZ_ORGANIZATION_PROFILES : ' || l_party_id.count);
l_sql_select_insert_ptp_intf varchar2(2000);
l_sql_from_insert_ptp_intf varchar2(2000);
l_sql_select_valid_party_id varchar2(2000);
l_sql_select_insert_ptp_prod varchar2(2000);
l_sql_from_insert_ptp_prod varchar2(3000);
CURSOR error_parties IS SELECT intf_party_reference
FROM zx_party_tax_profile_int zx_ptp
WHERE request_id = p_request_id
AND zx_ptp.intf_party_site_reference IS NULL
AND NVL(zx_ptp.record_status,3) = 3;
CURSOR error_sites IS SELECT intf_party_site_reference
FROM zx_party_tax_profile_int zx_ptp
WHERE request_id = p_request_id
AND zx_ptp.intf_party_site_reference IS NOT NULL
AND NVL(zx_ptp.record_status,3) = 3;
SELECT cust_tax_reference INTO l_cust_tax_reference
FROM ra_customers_interface
WHERE request_id = p_request_id
AND cust_tax_reference IS NOT NULL
AND ROWNUM = 1;
l_sql_select_insert_ptp_intf := 'SELECT cust_tax_reference,country,''CREATE'''||
',Nvl(orig_system_party_ref,orig_system_customer_ref) AS intf_party_reference'||
',NULL AS intf_party_site_reference'||
',''THIRD_PARTY''';
l_sql_from_insert_ptp_intf := ' FROM ra_customers_interface_all hz_rcia'||
' WHERE hz_rcia.insert_update_flag = ''I'''||
' AND hz_rcia.request_id = '||p_request_id||
' AND hz_rcia.cust_tax_reference IS NOT NULL';
l_sql_select_insert_ptp_intf := NULL;
l_sql_from_insert_ptp_intf := NULL;
FND_FILE.put_line(fnd_file.log,'l_sql_select_insert_ptp_intf : '||l_sql_select_insert_ptp_intf);
FND_FILE.put_line(fnd_file.log,'l_sql_from_insert_ptp_intf : '||l_sql_from_insert_ptp_intf);
, p_sql_select_insert_ptp_intf => l_sql_select_insert_ptp_intf
, p_sql_from_insert_ptp_intf => l_sql_from_insert_ptp_intf
, p_insert_only => FND_API.G_FALSE
, p_sql_select_valid_party_id => NULL
, p_sql_from_valid_party_id => NULL
, p_sql_select_insert_ptp_prod => NULL
, p_sql_from_insert_ptp_prod => NULL
, p_commit => FND_API.G_FALSE
, p_batch_id => null
, x_return_status => x_return_status
, x_msg_data => x_msg_data);
/* Update error status for all the errored out customers in ra_customers_interface_all*/
FORALL i IN 1..l_party_orig_sys_ref.Count
UPDATE ra_customers_interface_all rci
SET interface_status = interface_status|| 'z4,'
WHERE request_id = p_request_id
AND Nvl(orig_system_party_ref,orig_system_customer_ref) = l_party_orig_sys_ref(i);
/* Update error status for all the errored out addresses in ra_customers_interface_all*/
CLOSE error_parties;
UPDATE ra_customers_interface_all rci
SET interface_status = interface_status|| 'z5,'
WHERE request_id = p_request_id
AND orig_system_address_ref = l_address_orig_sys_ref(i);
ELSIF p_mode = 'INSERT' THEN
l_sql_select_valid_party_id := 'SELECT party_id, party_type_code'||
',intf_party_reference'||
',intf_party_site_reference';
l_sql_from_valid_party_id := ' FROM ((SELECT hz_insert.party_id, ''THIRD_PARTY'' as party_type_code'||
',hz_insert.orig_system_reference as intf_party_reference'||
',NULL as intf_party_site_reference'||
' FROM HZ_PARTIES hz_insert, ra_customers_interface hz_rcia'||
' WHERE hz_insert.orig_system_reference = nvl(hz_rcia.orig_system_party_ref, hz_rcia.orig_system_customer_ref)'||
' AND hz_insert.request_id = hz_rcia.request_id'||
' AND hz_rcia.interface_status is null'||
' AND hz_rcia.insert_update_flag = ''I'''||
' AND (hz_rcia.rowid = ( SELECT min(i2.rowid)'||
' FROM ra_customers_interface i2'||
' WHERE i2.orig_system_customer_ref = hz_rcia.orig_system_customer_ref'||
' AND i2.request_id = hz_rcia.request_id'||
' AND hz_rcia.orig_system_party_ref is null'||
' AND i2.interface_status is null'||
' AND i2.insert_update_flag = ''I'') OR'||
' hz_rcia.rowid = ( SELECT min(i2.rowid)'||
' FROM ra_customers_interface_all i2'||
' WHERE i2.orig_system_party_ref = hz_rcia.orig_system_party_ref'||
' AND i2.request_id = hz_rcia.request_id'||
' AND i2.interface_status is null'||
' AND i2.insert_update_flag = ''I''))'||
' AND (hz_insert.party_type =''ORGANIZATION'' OR hz_insert.party_type =''PERSON''))'||
' UNION ALL ' ||
'(SELECT ps.party_site_id as party_id, ''THIRD_PARTY_SITE'' as party_type_code'||
', NVL(rci.orig_system_party_ref, rci.orig_system_customer_ref) as intf_party_reference'||
',ps.orig_system_reference as intf_party_site_reference'||
' FROM HZ_PARTY_SITES ps, ra_customers_interface rci'||
' WHERE ps.orig_system_reference = rci.orig_system_address_ref'||
' AND ps.request_id = rci.request_id'||
' AND rci.interface_status is null' ||
' AND rci.insert_update_flag = ''I'''||
' AND (rci.rowid = ( SELECT min(i2.rowid)'||
' FROM ra_customers_interface i2'||
' WHERE i2.orig_system_address_ref = rci.orig_system_address_ref'||
' AND i2.request_id = rci.request_id'||
' AND i2.interface_status is NULL'||
' AND i2.insert_update_flag = ''I''))) '||
')';
l_sql_select_insert_ptp_prod := 'SELECT party_id, party_type_code, country country_code';
l_sql_from_insert_ptp_prod := ' FROM ((SELECT hz_insert.party_id, ''THIRD_PARTY'' as party_type_code, hz_insert.country'||
' FROM HZ_PARTIES hz_insert, ra_customers_interface hz_rcia'||
' WHERE hz_insert.orig_system_reference = nvl(hz_rcia.orig_system_party_ref, hz_rcia.orig_system_customer_ref)'||
' AND hz_insert.request_id = hz_rcia.request_id'||
' AND hz_rcia.interface_status is null'||
' AND hz_rcia.insert_update_flag = ''I'''||
' AND (hz_rcia.rowid = ( SELECT min(i2.rowid)'||
' FROM ra_customers_interface i2'||
' WHERE i2.orig_system_customer_ref = hz_rcia.orig_system_customer_ref'||
' AND i2.request_id = hz_rcia.request_id'||
' AND hz_rcia.orig_system_party_ref is null'||
' AND i2.interface_status is null'||
' AND i2.insert_update_flag = ''I'') OR'||
' hz_rcia.rowid = ( SELECT min(i2.rowid)'||
' FROM ra_customers_interface i2'||
' WHERE i2.orig_system_party_ref = hz_rcia.orig_system_party_ref'||
' AND i2.request_id = hz_rcia.request_id'||
' AND i2.interface_status is null'||
' AND i2.insert_update_flag = ''I''))'||
' AND (hz_insert.party_type =''ORGANIZATION'' OR hz_insert.party_type =''PERSON''))'||
' UNION ALL ' ||
'(SELECT ps.party_site_id as party_id, ''THIRD_PARTY_SITE'' as party_type_code, loc.country'||
' FROM HZ_PARTY_SITES ps, ra_customers_interface rci, HZ_LOCATIONS loc '||
' WHERE ps.orig_system_reference = rci.orig_system_address_ref'||
' AND loc.location_id = ps.location_id '||
' AND ps.request_id = rci.request_id'||
' AND rci.interface_status is null' ||
' AND rci.insert_update_flag = ''I'''||
' AND (rci.rowid = ( SELECT min(i2.rowid)'||
' FROM ra_customers_interface i2'||
' WHERE i2.orig_system_address_ref = rci.orig_system_address_ref'||
' AND i2.interface_status is NULL'||
' AND i2.request_id = rci.request_id'||
' AND i2.insert_update_flag = ''I'')))'||
' UNION ALL ' ||
'(SELECT party.party_id, ''THIRD_PARTY'' party_type_code, party.country' ||
' FROM HZ_PARTIES party, RA_CONTACT_PHONES_INTERFACE rcpi' ||
' WHERE party.orig_system_reference = rcpi.orig_system_contact_ref' ||
' AND party.request_id = rcpi.request_id'||
' AND rcpi.interface_status is null'||
' AND rcpi.insert_update_flag = ''I'''||
' AND rcpi.rowid = ( SELECT min(i2.rowid)'||
' FROM RA_CONTACT_PHONES_INTERFACE i2 '||
' WHERE i2.orig_system_contact_ref = rcpi.orig_system_contact_ref'||
' AND i2.interface_status is null'||
' AND i2.request_id = rcpi.request_id'||
' AND i2.insert_update_flag = ''I'')'||
'AND party.party_type =''PERSON'')'||
') hz_insert WHERE 1 = 1';
SELECT request_id INTO l_request_id
FROM zx_party_tax_profile_int
WHERE intf_party_reference IN (SELECT Nvl(orig_system_party_ref, orig_system_customer_ref)
FROM ra_customers_interface
)
AND ROWNUM = 1 ;
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);
, p_sql_select_insert_ptp_intf => NULL
, p_sql_from_insert_ptp_intf => NULL
, p_insert_only => FND_API.G_TRUE
, p_sql_select_valid_party_id => l_sql_select_valid_party_id
, p_sql_from_valid_party_id => l_sql_from_valid_party_id
, p_sql_select_insert_ptp_prod => l_sql_select_insert_ptp_prod
, p_sql_from_insert_ptp_prod => l_sql_from_insert_ptp_prod
, p_commit => FND_API.G_FALSE
, p_batch_id => NULL
, x_return_status => x_return_status
, x_msg_data => x_msg_data);
FND_FILE.put_line(fnd_file.log,'['||To_Char(SYSDATE,'DD-MON-RRRR HH24:MI:SS')||'] '||' End Call ZX_PTP_IMPORT.IMPORT_WRAPPER for INSERT with status :'||x_return_status);
FND_FILE.put_line(fnd_file.log,SYSDATE||' Error in Call ZX_PTP_IMPORT.IMPORT_WRAPPER for INSERT :'||x_msg_data);
SELECT request_id INTO l_request_id
FROM zx_party_tax_profile_int
WHERE intf_party_reference IN (SELECT Nvl(orig_system_party_ref, orig_system_customer_ref)
FROM ra_customers_interface
)
AND ROWNUM = 1 ;
SELECT request_id INTO l_request_id
FROM zx_party_tax_profile_int
WHERE intf_party_reference IN (SELECT Nvl(orig_system_party_ref, orig_system_customer_ref)
FROM ra_customers_interface
)
AND ROWNUM = 1 ;
ELSIF p_mode = 'DELETE_ZX_REC' THEN
IF validation_flag = 0 THEN
l_request_id := p_request_id;
SELECT request_id INTO l_request_id
FROM zx_party_tax_profile_int
WHERE intf_party_reference IN (SELECT Nvl(orig_system_party_ref, orig_system_customer_ref)
FROM ra_customers_interface
)
AND ROWNUM = 1 ;
FND_FILE.put_line(fnd_file.log,'Call ZX_PTP_IMPORT.delete_success_records');
ZX_PTP_IMPORT.delete_success_records(p_request_id => l_request_id
,p_commit => FND_API.G_FALSE
,x_return_status => x_return_status
,x_msg_data => x_msg_data);
FND_FILE.put_line(fnd_file.log,'End Call ZX_PTP_IMPORT.delete_success_records');
PROCEDURE delete_success_records IS
BEGIN
FND_FILE.put_line(fnd_file.log,'HZ_CUSTOMER_INT.delete_success_records +');
DELETE FROM ra_customers_interface
WHERE interface_status is NULL;
DELETE FROM RA_CONTACT_PHONES_INTERFACE
WHERE interface_status is null;
DELETE FROM ra_cust_pay_method_interface
WHERE interface_status is null;
DELETE FROM ra_customer_banks_interface
WHERE interface_status is null;
DELETE FROM ra_customer_profiles_interface
WHERE interface_status is null;
UPDATE ra_customers_interface
SET request_id = NULL;
UPDATE ra_customer_profiles_interface
SET request_id = NULL;
UPDATE ra_contact_phones_interface
SET request_id = NULL;
UPDATE ra_cust_pay_method_interface
SET request_id = NULL;
UPDATE ra_customer_banks_interface
SET request_id = NULL;
FND_FILE.put_line(fnd_file.log,'HZ_CUSTOMER_INT.delete_success_records -');
END delete_success_records;
select distinct ipi.ext_pmt_party_id, max(ipi.order_of_preference), count(ipi.instrument_payment_use_id)
FROM iby_external_payers_all iep,
iby_pmt_instr_uses_all ipi,
iby_ext_bank_accounts eba,
hz_cust_accounts hca,
ra_customer_banks_int_all cbi
WHERE
cbi.request_id = p_request_id
AND eba.currency_code = cbi.bank_account_currency_code
AND cbi.orig_system_customer_ref = hca.orig_system_reference
AND iep.cust_account_id = hca.cust_account_id
AND iep.party_id = hca.party_id
AND decode(cbi.orig_system_address_ref,null,-1,iep.org_id) = decode(cbi.orig_system_address_ref,null,-1,cbi.org_id)
AND decode(cbi.orig_system_address_ref,null,'OPERATING_UNIT',iep.org_type) = 'OPERATING_UNIT'
AND iep.ext_payer_id = ipi.ext_pmt_party_id
AND ipi.payment_function = 'CUSTOMER_PAYMENT'
AND ipi.payment_flow = 'FUNDS_CAPTURE'
AND ipi.instrument_type = 'BANKACCOUNT'
AND ipi.instrument_id = eba.ext_bank_account_id
AND cbi.interface_status is null
group by ipi.ext_pmt_party_id;
select ipi.instrument_payment_use_id
FROM iby_pmt_instr_uses_all ipi
WHERE ipi.ext_pmt_party_id = l_ext_pmt_party_id
AND ipi.order_of_preference = -1
order by instrument_payment_use_id;
update iby_pmt_instr_uses_all
set order_of_preference = j
where instrument_payment_use_id = i_instrument_payment_use_id(j);
update iby_pmt_instr_uses_all
set order_of_preference = i_max_order_of_preference(i) +j
where instrument_payment_use_id = i_instrument_payment_use_id(j);
PROCEDURE update_bank_priority (p_request_id IN NUMBER) IS
cursor get_cust_bank_acct_priority is
select ipi.instrument_payment_use_id, ipi.order_of_preference
FROM iby_external_payers_all iep,
iby_pmt_instr_uses_all ipi,
iby_ext_bank_accounts eba,
hz_cust_accounts hca,
ra_customer_banks_int_all cbi
WHERE cbi.orig_system_address_ref is null
AND cbi.request_id = p_request_id
AND cbi.primary_flag = 'Y'
AND eba.currency_code = cbi.bank_account_currency_code
AND cbi.orig_system_customer_ref = hca.orig_system_reference
AND iep.cust_account_id = hca.cust_account_id
AND iep.party_id = hca.party_id
AND iep.acct_site_use_id is null
AND iep.ext_payer_id = ipi.ext_pmt_party_id
AND ipi.payment_function = 'CUSTOMER_PAYMENT'
AND ipi.payment_flow = 'FUNDS_CAPTURE'
AND ipi.instrument_type = 'BANKACCOUNT'
AND ipi.instrument_id = eba.ext_bank_account_id
AND cbi.interface_status is null
order by ipi.order_of_preference desc;
select ipi.instrument_payment_use_id, ipi.order_of_preference
FROM iby_external_payers_all iep,
iby_pmt_instr_uses_all ipi,
iby_ext_bank_accounts eba,
hz_cust_acct_sites_all hca,
hz_cust_site_uses_all hcs,
hz_cust_accounts hcu,
ra_customer_banks_int_all cbi
WHERE cbi.request_id = p_request_id
AND cbi.primary_flag = 'Y'
AND cbi.orig_system_address_ref= hca.orig_system_reference
AND cbi.org_id = hca.org_id
AND hca.cust_acct_site_id = hcs.cust_acct_site_id
AND eba.currency_code = cbi.bank_account_currency_code
AND cbi.orig_system_customer_ref = hcu.orig_system_reference
AND iep.cust_account_id = hcu.cust_account_id
AND iep.party_id = hcu.party_id
AND iep.org_id = cbi.org_id
AND iep.org_type = 'OPERATING_UNIT'
AND iep.acct_site_use_id = hcs.site_use_id
AND nvl(hca.status,'A') = 'A'
AND nvl(hcs.status,'A') = 'A'
AND iep.ext_payer_id = ipi.ext_pmt_party_id
AND ipi.payment_function = 'CUSTOMER_PAYMENT'
AND ipi.payment_flow = 'FUNDS_CAPTURE'
AND ipi.instrument_type = 'BANKACCOUNT'
AND ipi.instrument_id = eba.ext_bank_account_id
AND cbi.interface_status is null
order by ipi.order_of_preference desc;
update iby_pmt_instr_uses_all
set order_of_preference = i_order_of_preference(i) +1
where instrument_payment_use_id = i_instrument_payment_use_id(i);
update iby_pmt_instr_uses_all
set order_of_preference = s_order_of_preference(i) +1
where instrument_payment_use_id = s_instrument_payment_use_id(i);
END update_bank_priority;