[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 /*+ leading(auas) use_nl(auas cpf) */ 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)
UNION
(SELECT unique ( CUR.CURRENCY_CODE ) FROM FND_CURRENCIES_VL CUR, AR_TRX_BAL_SUMMARY ATB, ar_irec_user_acct_sites_all AUAS
WHERE ATB.CURRENCY = CUR.CURRENCY_CODE AND
ATB.CUST_ACCOUNT_ID = AUAS.CUSTOMER_ID AND
(ATB.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID OR ATB.SITE_USE_ID IS NULL)
AND AUAS.user_id=FND_GLOBAL.USER_ID()
AND AUAS.session_id=p_session_id));
SELECT /*+ leading(auas) use_nl(auas cpf) */ 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 unique ( CUR.CURRENCY_CODE ) INTO l_default_currency FROM FND_CURRENCIES_VL CUR,
AR_TRX_BAL_SUMMARY ATB, ar_irec_user_acct_sites_all AUAS
WHERE ATB.CURRENCY = CUR.CURRENCY_CODE AND
ATB.CUST_ACCOUNT_ID = AUAS.CUSTOMER_ID AND
(ATB.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID OR ATB.SITE_USE_ID IS NULL)
AND AUAS.user_id=FND_GLOBAL.USER_ID()
AND AUAS.session_id=p_session_id;
select count(*) into l_currency_exist from dual where l_profile_default_currrency in
(( SELECT /*+ leading(auas) use_nl(auas cpf) */ 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)
UNION
(SELECT unique ( CUR.CURRENCY_CODE ) FROM FND_CURRENCIES_VL CUR, AR_TRX_BAL_SUMMARY ATB, ar_irec_user_acct_sites_all AUAS
WHERE ATB.CURRENCY = CUR.CURRENCY_CODE AND
ATB.CUST_ACCOUNT_ID = p_customer_id AND
(ATB.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID OR ATB.SITE_USE_ID IS NULL)
AND AUAS.user_id=FND_GLOBAL.USER_ID()
AND AUAS.session_id=p_session_id));
SELECT /*+ leading(auas) use_nl(auas cpf) */ 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 unique ( CUR.CURRENCY_CODE ) INTO l_default_currency FROM FND_CURRENCIES_VL CUR,
AR_TRX_BAL_SUMMARY ATB, ar_irec_user_acct_sites_all AUAS
WHERE ATB.CURRENCY = CUR.CURRENCY_CODE AND
ATB.CUST_ACCOUNT_ID = p_customer_id AND
(ATB.SITE_USE_ID = AUAS.CUSTOMER_SITE_USE_ID OR ATB.SITE_USE_ID IS NULL)
AND AUAS.user_id=FND_GLOBAL.USER_ID()
AND AUAS.session_id=p_session_id;
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 WEB_HTML_CALL INTO l_attr FROM FND_FORM_FUNCTIONS WHERE FUNCTION_NAME LIKE 'ARICONTACTUS';
SELECT WEB_HTML_CALL INTO p_output_string FROM FND_FORM_FUNCTIONS WHERE FUNCTION_NAME LIKE 'ARICONTACTUS';
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 branch_party_id bank_branch_id
FROM ce_bank_branches_v
WHERE branch_number = p_routing_number
and nvl(trunc(end_date), trunc(sysdate)) >= trunc(sysdate);
SELECT bank_name
FROM AR_BANK_DIRECTORY
WHERE routing_number = p_routing_number;
SELECT lengthb(p_num_to_strip)
INTO len_strip_num
FROM dual;
SELECT substrb(p_num_to_strip,i,1)
INTO l_strip_num_char(i)
FROM dual;
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,aps.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 hp.party_name,hca.account_number
into l_customer_acct_name,l_customer_acct_number
from hz_parties hp,hz_cust_accounts hca
where hp.party_id = hca.party_id
and hca.cust_account_id = l_customer_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;
select name from wf_local_roles
where upper(EMAIL_ADDRESS) = UPPER(p_email_address)
and ORIG_SYSTEM = 'WF_LOCAL_USERS'
and STATUS ='ACTIVE'
and USER_FLAG = 'Y'
order by last_update_date desc;
select role_name from wf_local_user_roles
where user_name = l_adhoc_user_name
and user_orig_system ='WF_LOCAL_USERS'
and role_orig_system ='WF_LOCAL_ROLES';
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,aps.class
into l_trx_number,l_trx_amt_due,l_trx_curr_code,l_trx_due_date,l_trx_term_name,l_trx_term_desc,l_trx_type
from ar_payment_schedules_all aps,ra_terms t
where aps.trx_number = l_trx_number
and aps.customer_id = p_customer_id
and aps.term_id = t.term_id(+);
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);
select customer_trx_id into l_cust_trx_id
from ar_payment_schedules_all
where payment_schedule_id = p_payment_schedule_id
and trx_number = p_txn_no;
Select (sysdate - ps.TRX_DATE), ra.Printing_option into l_txn_difference, l_print_flag
from ar_payment_schedules_all ps, ra_customer_trx_all ra
where ps.CUSTOMER_TRX_ID= ra.CUSTOMER_TRX_ID
and ps.trx_number=p_txn_no
and ps.CUSTOMER_TRX_ID = l_cust_trx_id;
Select (sysdate - TRX_DATE) into l_txn_difference from ar_payment_schedules_all
where trx_number=p_txn_no
and PAYMENT_SCHEDULE_ID = p_payment_schedule_id;
SELECT /*+ LEADING(RaCmRequests)*/ nvl(sum ( total_amount ), 0) into pending_cmreq_amt
FROM RA_CM_REQUESTS RaCmRequests, AR_PAYMENT_SCHEDULES ArPaymentSchedules, ar_irec_user_acct_sites_all AcctSites, ra_customer_trx ct
WHERE (RaCmRequests.CUSTOMER_TRX_ID = ArPaymentSchedules.CUSTOMER_TRX_ID) AND
nvl(ArPaymentSchedules.terms_sequence_number,1) = 1
AND AcctSites.user_id=FND_GLOBAL.USER_ID()
AND ArPaymentSchedules.customer_id = AcctSites.customer_id
AND AcctSites.customer_site_use_id=ArPaymentSchedules.customer_site_use_id
AND AcctSites.session_id=p_session_id
AND ( ArPaymentSchedules.class = 'INV' OR ArPaymentSchedules.class = 'GUAR' OR ArPaymentSchedules.class = 'CB' OR
ArPaymentSchedules.class = 'DM' OR ArPaymentSchedules.class = 'DEP' )
AND( ArPaymentSchedules.invoice_currency_code = p_currency_code ) AND
(RaCmRequests.status IN ('PENDING_APPROVAL','APPROVED_PEND_COMP'))
AND ArPaymentSchedules.customer_trx_id = ct.customer_trx_id
AND(TRUNC(ArPaymentSchedules.trx_date)) >= trunc(decode( nvl(FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'), 0), 0, ArPaymentSchedules.trx_date, (sysdate-FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'))))
AND ct.printing_option = decode(nvl(FND_PROFILE.VALUE('ARI_FILTER_DONOTPRINT_TRX'), 'NOT'), 'Y', 'PRI', ct.printing_option) ;
SELECT /*+ LEADING(RaCmRequests)*/ nvl(sum ( total_amount ), 0) into pending_cmreq_amt
FROM RA_CM_REQUESTS RaCmRequests, AR_PAYMENT_SCHEDULES ArPaymentSchedules, ar_irec_user_acct_sites_all AcctSites, ra_customer_trx ct
WHERE (RaCmRequests.CUSTOMER_TRX_ID = ArPaymentSchedules.CUSTOMER_TRX_ID) AND
nvl(ArPaymentSchedules.terms_sequence_number,1) = 1
AND AcctSites.user_id=FND_GLOBAL.USER_ID()
AND ArPaymentSchedules.customer_id=p_customer_id
AND ArPaymentSchedules.customer_id= AcctSites.customer_id
AND AcctSites.customer_site_use_id=ArPaymentSchedules.customer_site_use_id
AND AcctSites.session_id=p_session_id
AND ( ArPaymentSchedules.class = 'INV' OR ArPaymentSchedules.class = 'GUAR' OR ArPaymentSchedules.class = 'CB' OR
ArPaymentSchedules.class = 'DM' OR ArPaymentSchedules.class = 'DEP' )
AND( ArPaymentSchedules.invoice_currency_code = p_currency_code ) AND
(RaCmRequests.status IN ('PENDING_APPROVAL','APPROVED_PEND_COMP'))
AND ArPaymentSchedules.customer_trx_id = ct.customer_trx_id
AND(TRUNC(ArPaymentSchedules.trx_date)) >= trunc(decode( nvl(FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'), 0), 0, ArPaymentSchedules.trx_date, (sysdate-FND_PROFILE.VALUE('ARI_FILTER_TRXDATE_OLDER'))))
AND ct.printing_option = decode(nvl(FND_PROFILE.VALUE('ARI_FILTER_DONOTPRINT_TRX'), 'NOT'), 'Y', 'PRI', ct.printing_option) ;