[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT name,
salesrep_number
INTO l_default_srep_name,
l_default_srep_number
FROM RA_SALESREPS
WHERE SALESREP_ID = p_default_salesrep_id
AND NVL(status,'A') ='A'
AND p_deposit_date between nvl(start_date_active, p_deposit_date) and
nvl(end_date_active, p_deposit_date);
select distinct acct_role.cust_account_role_id contact_id
from hz_cust_account_roles acct_role,
hz_parties party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_parties rel_party
where acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id = rel.relationship_id
and rel.subject_id = party.party_id
and rel.party_id = rel_party.party_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and rel.directional_flag = 'F'
and acct_role.cust_account_id = p_bill_to_customer_id
and acct_role.cust_acct_site_id is null
/* the contact must be active. however, for credit memos
against specific transactions, the credited transaction's
contact may also be used even if it is not active. */
AND ( acct_role.cust_account_role_id = NULL
OR ( acct_role.status = 'A'))
ORDER BY 1;
select distinct acct_role.cust_account_role_id contact_id
from hz_cust_account_roles acct_role,
hz_parties party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_parties rel_party,
hz_cust_acct_sites acct_site,
hz_cust_site_uses site_uses
where acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id = rel.relationship_id
and rel.subject_id = party.party_id
and rel.party_id = rel_party.party_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and rel.directional_flag = 'F'
and acct_role.cust_account_id = p_bill_to_customer_id
and site_uses.site_use_id = p_bill_to_site_use_id
and acct_site.cust_account_id = acct_role.cust_account_id
/* show customer level as well as address level contacts */
and acct_role.cust_acct_site_id = site_uses.cust_acct_site_id
and acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
and site_uses.site_use_code = 'BILL_TO'
/* the contact must be active. however, for credit memos
against specific transactions, the credited transaction's
contact may also be used even if it is not active. */
AND ( acct_role.cust_account_role_id = NULL
OR ( acct_role.status = 'A') )
ORDER BY 1;
SELECT SALESREP_REQUIRED_FLAG,ORG_ID
into l_salesreprequiredflag,l_org_id
FROM AR_SYSTEM_PARAMETERS;
are defaulted after selecting the customer.
************************************************************************ */
/*-----------------------------------------------------+
| Default the Primary Salesrep : Hierarchy |
| |
| -- From the Bill to Site Value |
| -- From the Ship to Site Value |
| -- From Customer defaults (if Not Multi-Org) |
| -- To 'No Sales Credits' if Required_Flag='Y' |
|_____________________________________________________*/
/* Bill to */
begin
select su.primary_salesrep_id
into l_default_srep_id_1
from hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses su,
fnd_territories_vl t
where acct_site.cust_acct_site_id = su.cust_acct_site_id
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and loc.country = t.territory_code
and acct_site.cust_account_id = p_bill_to_customer_id
and su.site_use_id = nvl(p_bill_to_site_use_id, su.site_use_id)
and su.site_use_code = 'BILL_TO'
and ( su.site_use_id = null
or ( su.status = 'A'
and acct_site.status = 'A'
)
)
and su.primary_flag = 'Y';
select asa.primary_salesrep_id
/* selecting salesrep_id for Rel 11 */
into l_default_srep_id_2
from
(
SELECT
A.CUST_ACCOUNT_ID CUSTOMER_ID ,
A.STATUS A_STATUS ,
SU.PRIMARY_FLAG PRIMARY_FLAG ,
SU.STATUS SU_STATUS ,
SU.SITE_USE_ID SITE_USE_ID ,
SU.PRIMARY_SALESREP_ID
FROM
HZ_CUST_ACCT_SITES A,
HZ_CUST_SITE_USES SU
WHERE
A.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND SU.SITE_USE_CODE = 'SHIP_TO'
) asa
where asa.customer_id = p_ship_to_customer_id
and ( asa.site_use_id = p_ship_to_site_use_id
or ( asa.su_status = 'A'
and asa.a_status = 'A'
)
)
and asa.primary_flag='Y';
SELECT st.territory_id territory_id
FROM ra_salesrep_territories st
WHERE st.salesrep_id = p_salesrep_id
AND 'A' = NVL(st.status(+), 'A')
AND p_deposit_date BETWEEN NVL(st.start_date_active(+), p_deposit_date )
AND NVL(st.end_date_active(+), p_deposit_date );
select DEFAULT_TERRITORY
into l_territory_default
from ar_system_parameters;
arp_util.debug('EXCEPTIONS: Others system option selection');
select territory_id
into l_territory_id
from hz_cust_site_uses_all
where site_use_id = l_Site_Use_Id ;
select territory_id
into l_territory_id
from hz_cust_site_uses_all
where site_use_id =l_Site_Use_Id ;
select ship_via
into l_ship_via_ship_site_default
from hz_cust_site_uses_all
where site_use_id =l_Site_Use_Id ;
select ship_via into l_ship_via_ship_default
from hz_cust_accounts
where cust_account_id = p_ship_to_customer_id;
select ship_via
into l_ship_via_bill_site_default
from hz_cust_site_uses
where site_use_id = l_Site_Use_Id;
select ship_via
into l_ship_via_bill_default
from hz_cust_accounts
where cust_account_id = p_bill_to_customer_id;
select fob_point
into l_fob_ship_site_default
from hz_cust_site_uses
where site_use_id =l_Site_Use_Id ;
select fob_point into l_fob_ship_default
from hz_cust_accounts
where cust_account_id = p_ship_to_customer_id;
select fob_point
into l_fob_bill_site_default
from hz_cust_site_uses
where site_use_id = l_Site_Use_Id;
select fob_point
into l_fob_bill_default
from hz_cust_accounts
where cust_account_id = p_bill_to_customer_id;
l_selected_id NUMBER;
SELECT acct_role.cust_account_role_id
INTO l_selected_id
from hz_cust_account_roles acct_role,
hz_parties party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_parties rel_party
where acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id =
rel.relationship_id
and rel.subject_id = party.party_id
and rel.party_id = rel_party.party_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and rel.directional_flag = 'F'
and acct_role.cust_account_id = p_customer_id
/* the contact must be active. however, for credit memos
against specific transactions, the credited transaction's
contact may also be used even if it is not active. */
AND acct_role.status = 'A'
AND party.person_last_name = p_person_last_name
AND party.person_first_name = p_person_first_name;
l_selected_id := null;
l_selected_id := null;
return(l_selected_id);
l_selected_id VARCHAR2(100);
l_selected_id := null;
SELECT c.cust_account_id
INTO l_selected_id
FROM hz_cust_accounts c,
hz_customer_profiles cp,
hz_parties party
WHERE c.cust_account_id = cp.cust_account_id (+) and
cp.site_use_id is null and
c.account_number = p_value and
c.status <> 'I'
AND c.party_id = party.party_id;
SELECT cust_acct.cust_account_id
INTO l_selected_id
FROM hz_cust_accounts cust_acct,
hz_customer_profiles cp,
hz_parties party
WHERE cust_acct.cust_account_id = cp.cust_account_id (+)
and cust_acct.party_id = party.party_id(+)
and cp.site_use_id is null
and cust_acct.status <> 'I'
and party.party_name = p_value;
SELECT salesrep_id
INTO l_selected_id
FROM ra_salesreps
WHERE name = p_value;
SELECT batch_source_id
INTO l_selected_id
FROM ra_batch_sources
WHERE name = p_value and
nvl(status, 'A') = 'A' and (batch_source_type = 'INV');
SELECT term_id
INTO l_selected_id
FROM ra_terms
WHERE name = p_value;
SELECT receipt_method_id
INTO l_selected_id
FROM ar_receipt_methods
WHERE name = p_value;
SELECT bank_account_id
INTO l_selected_id
FROM ce_bank_accounts
WHERE bank_account_num = p_value
AND pg_deposit_date < NVL(end_date,
TO_DATE('01/01/2200',
'DD/MM/YYYY') );
SELECT bank_account_id
INTO l_selected_id
FROM ce_bank_accounts
WHERE bank_account_name = p_value
AND pg_deposit_date < NVL(end_date,
TO_DATE('01/01/2200',
'DD/MM/YYYY') );
SELECT currency_code
INTO l_selected_id
FROM fnd_currencies_vl
WHERE name = p_value;
SELECT conversion_type
INTO l_selected_id
FROM gl_daily_conversion_types
WHERE user_conversion_type = p_value ;
RETURN( l_selected_id );
l_selected_id hz_cust_site_uses.site_use_id%type;
SELECT site_use.site_use_id
INTO l_selected_id
FROM hz_cust_site_uses site_use,
hz_cust_acct_sites acct_site
WHERE acct_site.cust_account_id = p_customer_id
AND acct_site.status = 'A'
AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
AND (site_use.site_use_code = nvl(p_site_use_code1,
site_use.site_use_code) OR
site_use.site_use_code = nvl(p_site_use_code1,
site_use.site_use_code))
AND site_use.status = 'A'
AND site_use.location = p_location;
SELECT site_use.site_use_id
INTO l_selected_id
FROM hz_cust_site_uses site_use,
hz_cust_acct_sites acct_site
WHERE acct_site.cust_account_id = p_customer_id
AND acct_site.status = 'A'
AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
AND (site_use.site_use_code = nvl(p_site_use_code1,
site_use.site_use_code) OR
site_use.site_use_code = nvl(p_site_use_code1,
site_use.site_use_code))
AND site_use.status = 'A'
AND site_use.primary_flag = 'Y';
RETURN( l_selected_id );
SELECT term_id
INTO x_term_id
FROM ra_terms
WHERE name = p_term_name and
nvl(p_deposit_date, trunc(sysdate)) between start_date_active
and nvl(end_date_active,nvl(p_deposit_date,trunc(sysdate)));
SELECT term_id
INTO x_term_id
FROM ra_terms
WHERE term_id = x_term_id and
nvl(p_deposit_date, trunc(sysdate)) between start_date_active
and nvl(end_date_active,nvl(p_deposit_date,trunc(sysdate)));
SELECT salesrep_id
INTO x_salesrep_id
FROM ra_salesreps
WHERE name = p_salesrep_name and
NVL(status,'A') ='A' and
p_deposit_date between nvl(start_date_active, p_deposit_date) and
nvl(end_date_active, p_deposit_date);
SELECT salesrep_id
INTO x_salesrep_id
FROM ra_salesreps
WHERE salesrep_id = x_salesrep_id and
NVL(status,'A') ='A' and
p_deposit_date between nvl(start_date_active, p_deposit_date) and
nvl(end_date_active, p_deposit_date);
select memo_line_id
into x_memo_line_id
from ar_memo_lines
where line_type='LINE' and
sysdate between nvl(trunc(start_date),sysdate)
and nvl(trunc(end_date),sysdate) and
name = p_memo_line_name;
select memo_line_id
into l_dummy
from ar_memo_lines
where line_type='LINE' and
sysdate between nvl(trunc(start_date),sysdate)
and nvl(trunc(end_date),sysdate) and
memo_line_id = x_memo_line_id;
select inventory_item_id
into l_dummy
from MTL_SYSTEM_ITEMS_B
where inventory_item_id = x_inventory_id and
ORGANIZATION_ID = oe_profile.value('SO_ORGANIZATION_ID') and
ENABLED_FLAG = 'Y';
/* SELECT batch_source_id
INTO x_batch_source_id
FROM ra_batch_sources
WHERE name = p_batch_source_name and
batch_source_id not in (11,12) and
nvl(status,'A')= 'A' and (batch_source_type = 'INV');*/
SELECT batch_source_id
INTO x_batch_source_id
FROM ra_batch_sources bs
WHERE name = p_batch_source_name and
batch_source_id not in (11,12) and
nvl(status,'A')= 'A' and (batch_source_type = 'INV') and
(( p_deposit_number is not null and
auto_trx_numbering_flag = 'N' ) or
( p_deposit_number is null and
auto_trx_numbering_flag = 'Y' ) ) and
nvl(p_deposit_date, trunc(sysdate)) between nvl(bs.start_date, nvl(p_deposit_date, trunc(sysdate))) and
nvl(bs.end_date, nvl(p_deposit_date, trunc(sysdate)));
/* SELECT batch_source_id
INTO l_dummy
FROM ra_batch_sources
WHERE batch_source_id = x_batch_source_id and
batch_source_id not in (11,12) and
nvl(status,'A')= 'A' and (batch_source_type = 'INV');*/
SELECT batch_source_id
INTO l_dummy
FROM ra_batch_sources bs
WHERE batch_source_id = x_batch_source_id and
batch_source_id not in (11,12) and
nvl(status,'A')= 'A' and (batch_source_type = 'INV') and
(( p_deposit_number is not null and
auto_trx_numbering_flag = 'N' ) or
( p_deposit_number is null and
auto_trx_numbering_flag = 'Y' ) ) and
nvl(p_deposit_date, trunc(sysdate)) between nvl(bs.start_date, nvl(p_deposit_date, trunc(sysdate))) and
nvl(bs.end_date, nvl(p_deposit_date, trunc(sysdate)));
SELECT cust_trx_type_id
INTO x_cust_trx_type_id
FROM ra_cust_trx_types
where type = 'DEP' and
nvl(p_deposit_date, trunc(sysdate)) between
nvl(start_date(+), nvl(p_deposit_date, trunc(sysdate))) and
nvl(end_date(+), nvl(p_deposit_date, trunc(sysdate))) and
NAME = p_cust_trx_type;
SELECT cust_trx_type_id
INTO l_dummy
FROM ra_cust_trx_types
where type = 'DEP' and
nvl(p_deposit_date, trunc(sysdate)) between
nvl(start_date(+), nvl(p_deposit_date, trunc(sysdate))) and
nvl(end_date(+), nvl(p_deposit_date, trunc(sysdate))) and
cust_trx_type_id = x_cust_trx_type_id;
SELECT cust.cust_account_id
INTO l_customer_id
FROM hz_cust_accounts cust,
hz_customer_profiles cp,
hz_parties party
WHERE cust.cust_account_id = cp.cust_account_id (+) and
cp.site_use_id is null and
cust.cust_account_id = x_bill_to_customer_id and
cust.status <> 'I' and
cust.party_id = party.party_id;
SELECT cust.cust_account_id
INTO l_customer_id
FROM hz_cust_accounts cust,
hz_customer_profiles cp,
hz_parties party
WHERE cust.cust_account_id = cp.cust_account_id (+) and
cp.site_use_id is null and
cust.cust_account_id = x_ship_to_customer_id and
cust.status <> 'I' and
cust.party_id = party.party_id;
select su.location
into l_dft_bill_to_location
from hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses su,
fnd_territories_vl t
where acct_site.cust_acct_site_id = su.cust_acct_site_id
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and loc.country = t.territory_code
and acct_site.cust_account_id = x_bill_to_customer_id
and su.site_use_id = nvl(null,su.site_use_id)
and su.site_use_code = 'BILL_TO'
and ( su.site_use_id = null
or ( su.status = 'A'
and acct_site.status = 'A'
)
)
and su.primary_flag = 'Y';
select su.location
into l_dft_ship_to_location
from hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses su,
fnd_territories_vl t
where acct_site.cust_acct_site_id = su.cust_acct_site_id
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and loc.country = t.territory_code
and acct_site.cust_account_id = x_ship_to_customer_id
and su.site_use_id = nvl(null,su.site_use_id)
and su.site_use_code = 'SHIP_TO'
and ( su.site_use_id = null
or ( su.status = 'A'
and acct_site.status = 'A'
)
)
and su.primary_flag = 'Y';
SELECT acct_role.cust_account_role_id
INTO l_dummy
from hz_cust_account_roles acct_role,
hz_parties party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_parties rel_party
where acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id =
rel.relationship_id
and rel.subject_id = party.party_id
and rel.party_id = rel_party.party_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and rel.directional_flag = 'F'
and acct_role.cust_account_id = x_bill_to_customer_id
/* the contact must be active. however, for credit memos
against specific transactions, the credited transaction's
contact may also be used even if it is not active. */
AND acct_role.status = 'A'
AND acct_role.cust_account_role_id = x_bill_to_contact_id;
SELECT acct_role.cust_account_role_id
INTO l_dummy
from hz_cust_account_roles acct_role,
hz_parties party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_parties rel_party
where acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id =
rel.relationship_id
and rel.subject_id = party.party_id
and rel.party_id = rel_party.party_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and rel.directional_flag = 'F'
and acct_role.cust_account_id = x_ship_to_customer_id
/* the contact must be active. however, for credit memos
against specific transactions, the credited transaction's
contact may also be used even if it is not active. */
AND acct_role.status = 'A'
AND acct_role.cust_account_role_id = x_ship_to_contact_id;
SELECT name
INTO p_receipt_method_name
FROM ar_receipt_methods
WHERE receipt_method_id = x_receipt_method_id;
select address_id into l_dummy
from ar_active_remit_to_addresses_v
where address_id = x_remit_to_address_id;
SELECT cust.cust_account_id
INTO l_customer_id
FROM hz_cust_accounts cust,
hz_customer_profiles cp,
hz_parties party
WHERE cust.cust_account_id = cp.cust_account_id (+) and
cp.site_use_id is null and
cust.cust_account_id = x_sold_to_customer_id and
cust.status <> 'I' and
cust.party_id = party.party_id;
SELECT cust.cust_account_id
INTO l_customer_id
FROM hz_cust_accounts cust,
hz_customer_profiles cp,
hz_parties party
WHERE cust.cust_account_id = cp.cust_account_id (+) and
cp.site_use_id is null and
cust.cust_account_id = x_paying_customer_id and
cust.status <> 'I' and
cust.party_id = party.party_id;
select su.site_use_id
into x_paying_customer_site_use_id
from hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses su,
fnd_territories_vl t
where acct_site.cust_acct_site_id = su.cust_acct_site_id
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and loc.country = t.territory_code
and acct_site.cust_account_id = x_paying_customer_id
and su.site_use_id = nvl(null,su.site_use_id)
and su.site_use_code = 'BILL_TO'
and ( su.site_use_id = null
or ( su.status = 'A'
and acct_site.status = 'A'
)
)
and su.primary_flag = 'Y';
Select trunc(sysdate)
into p_deposit_date
from dual;
SELECT 'Y'
FROM fnd_descriptive_flexs
WHERE application_id = 222 and
descriptive_flexfield_name = p_desc_flex_name;
select title
into l_flex_title
from FND_DESCRIPTIVE_FLEXS_VL
where DESCRIPTIVE_FLEXFIELD_NAME=p_flex_name;