[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
INTO user_access
FROM dual
WHERE p_customer_id IN (SELECT cust_account_id
FROM ar_customers_assigned_v
WHERE party_id = p_person_party_id);
SELECT 'Y'
FROM ar_sites_assigned_v a,HZ_CUST_SITE_USES b
where a.cust_acct_site_id = b.cust_acct_site_id
and b.SITE_USE_CODE = 'BILL_TO'
AND party_id = p_person_party_id and site_use_id = p_customer_site_use_id;
SELECT 'Y'
FROM ar_customers_assigned_v Custs_assigned,
hz_cust_acct_sites Site,HZ_CUST_SITE_USES site_uses
WHERE Custs_assigned.party_id = p_person_party_id
AND Site.cust_account_id =
Custs_assigned.cust_account_id
and Site.cust_acct_site_id =
site_uses.cust_acct_site_id
and site_uses.SITE_USE_CODE = 'BILL_TO' and site_uses.SITE_USE_ID = p_customer_site_use_id;
SELECT meaning
INTO l_meaning
FROM ar_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
SELECT
SITE_USE_CODE, SITE_USE_ID
FROM
hz_cust_site_uses
WHERE
cust_acct_site_id = addr_id;
RETURN 'EXISTS (SELECT 1
FROM mo_glob_org_access_tmp oa
WHERE oa.organization_id = org_id
OR org_id = -1)';
select count(*) into l_currency_exist from dual where l_profile_default_currrency in
( SELECT unique ( CUR.CURRENCY_CODE ) FROM HZ_CUST_PROFILE_AMTS CPA,
FND_CURRENCIES_VL CUR, HZ_CUSTOMER_PROFILES CPF, ar_irec_user_acct_sites_all AUAS
WHERE CPA.CURRENCY_CODE = CUR.CURRENCY_CODE
AND CPF.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
AND CPF.CUST_ACCOUNT_ID =AUAS.CUSTOMER_ID
AND (
CPF.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID
OR
CPF.SITE_USE_ID IS NULL
)
AND AUAS.user_id=FND_GLOBAL.USER_ID()
AND AUAS.session_id=p_session_id);
SELECT unique ( CUR.CURRENCY_CODE )
INTO l_default_currency
FROM HZ_CUST_PROFILE_AMTS CPA,
FND_CURRENCIES_VL CUR,
HZ_CUSTOMER_PROFILES CPF,
ar_irec_user_acct_sites_all AUAS
WHERE CPA.CURRENCY_CODE = CUR.CURRENCY_CODE
AND CPF.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
AND CPF.CUST_ACCOUNT_ID =AUAS.CUSTOMER_ID
AND (
CPF.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID
OR
CPF.SITE_USE_ID IS NULL
)
AND AUAS.user_id=FND_GLOBAL.USER_ID()
AND AUAS.session_id=p_session_id
AND ROWNUM = 1;
select count(*) into l_currency_exist from dual where l_profile_default_currrency in
( SELECT unique ( CUR.CURRENCY_CODE ) FROM HZ_CUST_PROFILE_AMTS CPA,
FND_CURRENCIES_VL CUR, HZ_CUSTOMER_PROFILES CPF, ar_irec_user_acct_sites_all AUAS
WHERE CPA.CURRENCY_CODE = CUR.CURRENCY_CODE
AND CPF.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
AND CPF.CUST_ACCOUNT_ID = p_customer_id
AND (
CPF.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID
OR
CPF.SITE_USE_ID IS NULL
)
AND AUAS.user_id=FND_GLOBAL.USER_ID()
AND AUAS.session_id=p_session_id);
SELECT unique ( CUR.CURRENCY_CODE )
INTO l_default_currency
FROM HZ_CUST_PROFILE_AMTS CPA,
FND_CURRENCIES_VL CUR,
HZ_CUSTOMER_PROFILES CPF,
ar_irec_user_acct_sites_all AUAS
WHERE
CPA.CURRENCY_CODE = CUR.CURRENCY_CODE AND
CPF.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID AND
CPF.CUST_ACCOUNT_ID = p_customer_id AND
(
CPF.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID
OR
CPF.SITE_USE_ID IS NULL
)
AND AUAS.user_id=FND_GLOBAL.USER_ID()
AND AUAS.session_id=p_session_id
AND ROWNUM = 1;
SELECT sb.currency_code
INTO l_default_currency
FROM ar_system_parameters sys,
gl_sets_of_books sb
WHERE sb.set_of_books_id = sys.set_of_books_id;
SELECT 'Y'
INTO user_access
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM ar_sites_assigned_v a,HZ_CUST_SITE_USES b
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.SITE_USE_CODE = 'BILL_TO'
AND party_id = p_person_party_id
AND site_use_id = p_customer_site_use_id );
SELECT 'Y'
INTO user_access
FROM dual
WHERE p_customer_id IN (
select hca.cust_account_id
from hz_relationships hr,
hz_parties hp1,
hz_parties hp2,
hz_cust_accounts hca
where hr.subject_id = hp1.party_id
and hr.object_id = hp2.party_id
and subject_table_name = 'HZ_PARTIES'
and object_table_name = 'HZ_PARTIES'
and hr.relationship_type IN ( 'EMPLOYMENT', 'CONTACT')
and hr.subject_id = p_person_party_id
and hca.party_id = hp2.party_id);
select contact_id from (
select SUB.cust_account_role_id contact_id, SUB.CUST_ACCT_SITE_ID , SROLES.responsibility_type ,SROLES.PRIMARY_FLAG ,
row_number() OVER ( partition by SROLES.responsibility_type , SUB.CUST_ACCT_SITE_ID order by SROLES.PRIMARY_FLAG DESC NULLS LAST, SUB.last_update_date desc) last_update_record,
decode(SROLES.responsibility_type,p_contact_role_type,111,999) resp_code
from hz_cust_account_roles SUB,
hz_role_responsibility SROLES
where SUB.cust_account_role_id = SROLES.CUST_ACCOUNT_ROLE_ID AND
SUB.status = 'A' AND
SUB.CUST_ACCOUNT_ID = p_customer_id
AND ( SUB.CUST_ACCT_SITE_ID = p_customer_site_use_id)
)
where last_update_record <=1
ORDER BY resp_code ASC, CUST_ACCT_SITE_ID ASC NULLS LAST ;
select contact_id from (
select SUB.cust_account_role_id contact_id, SUB.CUST_ACCT_SITE_ID , SROLES.responsibility_type ,SROLES.PRIMARY_FLAG ,
row_number() OVER ( partition by SROLES.responsibility_type , SUB.CUST_ACCT_SITE_ID order by SROLES.PRIMARY_FLAG DESC NULLS LAST, SUB.last_update_date desc) last_update_record,
decode(SROLES.responsibility_type,p_contact_role_type,111,999) resp_code
from hz_cust_account_roles SUB,
hz_role_responsibility SROLES
where SUB.cust_account_role_id = SROLES.CUST_ACCOUNT_ROLE_ID AND
SUB.status = 'A' AND
SUB.CUST_ACCOUNT_ID = p_customer_id
AND (SUB.CUST_ACCT_SITE_ID IS NULL)
)
where last_update_record <=1
ORDER BY resp_code ASC, CUST_ACCT_SITE_ID ASC NULLS LAST ;
SELECT LTRIM(substrb(PARTY.PERSON_FIRST_NAME,1,40) || ' ') ||
substrb(PARTY.PERSON_LAST_NAME,1,50)
INTO l_contact_name
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_RELATIONSHIPS REL
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = l_contact_id
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND DIRECTIONAL_FLAG = 'F';
SELECT LTRIM(substrb(PARTY.PERSON_FIRST_NAME,1,40) || ' ') ||
substrb(PARTY.PERSON_LAST_NAME,1,50)
INTO l_contact_name
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_RELATIONSHIPS REL
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND DIRECTIONAL_FLAG = 'F';
SELECT phone_id FROM
( SELECT CONT_POINT.CONTACT_POINT_ID phone_id,
row_number() OVER ( order by CONT_POINT.last_update_date desc) last_update_record
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_CONTACT_POINTS CONT_POINT
WHERE
ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
AND ACCT_ROLE.PARTY_ID = CONT_POINT.OWNER_TABLE_ID
AND CONT_POINT.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND CONT_POINT.STATUS = 'A'
AND INSTRB(NVL(CONT_POINT.PHONE_LINE_TYPE, CONT_POINT.CONTACT_POINT_TYPE) || 'ALL', p_phone_type) > 0
AND CONT_POINT.PRIMARY_FLAG = p_primary_flag
)
WHERE last_update_record<=1;
SELECT RTRIM(LTRIM(cont_point.PHONE_AREA_CODE || '-' ||
DECODE(CONT_POINT.CONTACT_POINT_TYPE,'TLX',
CONT_POINT.TELEX_NUMBER,
CONT_POINT.PHONE_NUMBER)||'-'||
CONT_POINT.PHONE_EXTENSION, '-'), '-')
INTO l_contact_phone
FROM HZ_CONTACT_POINTS CONT_POINT
WHERE CONT_POINT.CONTACT_POINT_ID = l_phone_id;
SELECT phone_id FROM
( SELECT CONT_POINT.CONTACT_POINT_ID phone_id,
row_number() OVER ( order by CONT_POINT.last_update_date desc) last_update_record
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_CONTACT_POINTS CONT_POINT
WHERE
ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
AND ACCT_ROLE.PARTY_ID = CONT_POINT.OWNER_TABLE_ID
AND CONT_POINT.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND CONT_POINT.STATUS = 'A'
AND INSTRB(NVL(CONT_POINT.PHONE_LINE_TYPE, CONT_POINT.CONTACT_POINT_TYPE) || 'ALL', p_phone_type) > 0
AND CONT_POINT.PRIMARY_FLAG = p_primary_flag
)
WHERE last_update_record<=1;
SELECT RTRIM(LTRIM(cont_point.PHONE_AREA_CODE || '-' ||
DECODE(CONT_POINT.CONTACT_POINT_TYPE,'TLX',
CONT_POINT.TELEX_NUMBER,
CONT_POINT.PHONE_NUMBER)||'-'||
CONT_POINT.PHONE_EXTENSION, '-'), '-')
INTO l_contact_phone
FROM HZ_CONTACT_POINTS CONT_POINT
WHERE CONT_POINT.CONTACT_POINT_ID = l_phone_id;
SELECT IREC_SERVICE_CHARGE_REC_TRX_ID FROM AR_SYSTEM_PARAMETERS;
SELECT
unique( LOCATION)
FROM
hz_cust_site_uses
WHERE
cust_acct_site_id = addr_id
AND status = 'A' ;
SELECT
SITE_USE_CODE, SITE_USE_ID
FROM
hz_cust_site_uses
WHERE
cust_acct_site_id = addr_id;
select count(*) into l_account_access_count from ar_customers_assigned_v hzca where hzca.cust_account_id = p_customer_id
and hzca.party_id=p_party_id;
select count(*) into l_site_access_count from ar_sites_assigned_v acct_sites_count
where acct_sites_count.party_id=p_party_id
and acct_sites_count.cust_account_id=p_customer_id
and INSTR(ARI_UTILITIES.GET_SITE_USE_CODE(acct_sites_count.CUST_ACCT_SITE_ID), 'BILL_TO')>0;
select count(*) into l_flag from(
select trx_number,CUSTOMER_SITE_USE_ID from ar_payment_schedules where trx_number=p_trx_number
and CUSTOMER_SITE_USE_ID in
(
select ARI_UTILITIES.get_bill_to_site_use_id(CUST_ACCT_SITE_ID) from ar_sites_assigned_v where
party_id=p_party_id
and cust_account_id=p_customer_id
)
);
select aps.customer_id,sites.CUST_ACCT_SITE_ID,trx_number
into l_customer_id,l_customer_acct_site_id,l_trx_number
from ar_payment_schedules_all aps,HZ_CUST_SITE_USES sites
where aps.customer_trx_id = l_customer_trx_id
and aps.org_id = l_org_id
and sites.site_use_id = aps.customer_site_use_id;
SELECT hcar.CUST_ACCOUNT_ROLE_ID as contact_id
FROM HZ_CUST_ACCOUNT_ROLES hcar, HZ_PARTIES hpsub, HZ_PARTIES hprel,
HZ_ORG_CONTACTS hoc, HZ_RELATIONSHIPS hr, HZ_PARTY_SITES hps, FND_TERRITORIES_VL ftv,
fnd_lookup_values_vl lookups,hz_role_responsibility hrr
WHERE hrr.responsibility_type = 'SELF_SERVICE_USER'
and hrr.cust_account_role_id = hcar.cust_account_role_id
and hcar.CUST_ACCOUNT_ID = p_customer_id
AND hcar.ROLE_TYPE = 'CONTACT'
AND hcar.PARTY_ID = hr.PARTY_ID
AND hr.PARTY_ID = hprel.PARTY_ID
AND hr.SUBJECT_ID = hpsub.PARTY_ID
AND hoc.PARTY_RELATIONSHIP_ID = hr.RELATIONSHIP_ID
AND hr.DIRECTIONAL_FLAG = 'F'
AND hps.PARTY_ID(+) = hprel.PARTY_ID
AND nvl(hps.IDENTIFYING_ADDRESS_FLAG, 'Y') = 'Y'
AND nvl(hps.STATUS, 'A') = 'A'
AND hprel.COUNTRY = ftv.TERRITORY_CODE(+)
AND nvl(hcar.CUST_ACCT_SITE_ID, 1) = nvl(p_customer_acct_site_id, 1)
AND lookups.LOOKUP_TYPE (+)='RESPONSIBILITY'
AND lookups.LOOKUP_CODE(+)=hoc.JOB_TITLE_CODE
and hcar.status='A';
SELECT cont_point.email_Address
FROM hz_cust_account_roles acct_role,
hz_contact_points cont_point
WHERE acct_role.cust_account_role_id =l_contact_id
AND acct_role.party_id = cont_point.owner_table_id
AND cont_point.owner_table_name = 'HZ_PARTIES'
AND cont_point.status = 'A'
AND cont_point.email_Address is not null;
l_debug_info := 'selecting all the attributes required to send in notification';
select aps.trx_number,aps.amount_due_original,aps.invoice_currency_code,aps.due_date,t.name,t.description
into l_trx_number,l_inv_amt_due,l_inv_curr_code,l_inv_due_date,l_term_nanme,l_term_desc
from ar_payment_schedules_all aps,ra_terms t
where aps.customer_trx_id = l_customer_trx_id
and aps.customer_id = p_customer_id
and aps.term_id = t.term_id(+);
select count(1),name
into l_role_exists,l_adhoc_user_name
from WF_LOCAL_ROLES
where EMAIL_ADDRESS = l_email
group by name;
SELECT item_type
FROM wf_items
WHERE item_key = l_item_key
AND item_type IN('ARCMREQ','ARAMECM');
SELECT payment_schedule_id,
due_date,
amount_in_dispute,
dispute_date
FROM ar_payment_schedules ps
WHERE ps.customer_trx_id = p_customer_trx_id;
select party_id
from hz_cust_accounts
where cust_account_id = p_cust_acct_id;
select bill_to_site_use_id
from ra_customer_trx
where customer_trx_id = p_cust_trx_id;
select customer_id,payment_schedule_id
from ar_payment_schedules
where customer_trx_id = p_cust_trx_id;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_last_update_date DATE;
SELECT total_amount * -1
INTO remove_from_dispute_amt
FROM ra_cm_requests
WHERE request_id = p_dispute_id;
arp_process_cutil.update_ps(p_ps_id => ps_rec.payment_schedule_id,
p_due_date => ps_rec.due_date,
p_amount_in_dispute => new_dispute_amt,
p_dispute_date => new_dispute_date,
p_update_dff => 'N',
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL);
l_last_updated_by := arp_global.user_id;
l_last_update_login := arp_global.last_update_login;
SELECT customer_trx_id
INTO l_customer_trx_id
FROM ra_cm_requests
WHERE request_id = l_document_id;
arp_notes_pkg.insert_cover(
p_note_type => 'MAINTAIN',
p_text => l_note_text,
p_customer_call_id => null,
p_customer_call_topic_id => null,
p_call_action_id => NULL,
p_customer_trx_id => l_customer_trx_id,
p_note_id => l_note_id,
p_last_updated_by => l_last_updated_by,
p_last_update_date => l_last_update_date,
p_last_update_login => l_last_update_login,
p_created_by => l_created_by,
p_creation_date => l_creation_date);