[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT bank_account_num
FROM ap_bank_accounts
WHERE bank_account_id = X_Bank_Account_Id
AND bank_branch_id=1
AND account_type='EXTERNAL';
SELECT customer_id
FROM oe_invoice_to_orgs_v
WHERE organization_id = X_Invoice_To_Org_Id;
SELECT sold_to_org_id, invoice_to_org_id
from oe_order_headers_all
where header_id = x_header_id;
SELECT sold_to_org_id, invoice_to_org_id
from oe_order_lines_all
where line_id = x_line_id;
Select party_site.party_id, acct_site.cust_account_id
Into l_party_id, l_cust_account_id
From HZ_CUST_SITE_USES_ALL SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_CUST_ACCT_SITES ACCT_SITE
Where SITE.SITE_USE_ID = l_invoice_to_org_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
SELECT bank_account_name
FROM ap_bank_accounts
WHERE bank_account_id = X_Bank_Account_Id
AND bank_branch_id=1
AND account_type='EXTERNAL';
SELECT bank_account_name
FROM ap_bank_accounts
WHERE bank_account_num=X_CC_Number
AND bank_branch_id=1
AND account_type='EXTERNAL'
AND rownum=1;
SELECT sold_to_org_id, invoice_to_org_id
from oe_order_headers_all
where header_id = x_header_id;
SELECT sold_to_org_id, invoice_to_org_id
from oe_order_lines_all
where line_id = x_line_id;
SELECT to_char(inactive_date,'DD-MON-YYYY')
FROM ap_bank_accounts
WHERE bank_account_id = X_Bank_Account_Id
AND bank_branch_id=1
AND account_type='EXTERNAL';
SELECT to_char(inactive_date,'DD-MON-YYYY')
FROM ap_bank_accounts
WHERE bank_account_num=X_CC_Number
AND bank_branch_id=1
AND account_type='EXTERNAL'
AND rownum=1;
SELECT tax.tax_exempt_number
FROM tax_exemptions_qp_v tax,
oe_ship_to_orgs_v s,
hr_organization_information hr,
hz_cust_site_uses site --ra_site_uses (bug fix 2116858)
WHERE tax.ship_to_site_use_id = s.site_use_id
AND s.organization_id = X_Ship_To_Org_Id
AND tax.bill_to_customer_id = hr.org_information1
AND hr.organization_id = X_Invoice_To_Org_Id
and hr.org_information_context = 'Customer/Supplier Association'
and SITE.SITE_USE_ID = TO_NUMBER ( hr.ORG_INFORMATION2 )
and SITE.SITE_USE_CODE = 'BILL_TO'
AND trunc(NVL(X_Date_Ordered, SYSDATE))
between trunc(tax.start_date) and
trunc(NVL(tax.end_date, NVL(X_Date_Ordered, SYSDATE)))
AND tax.status_code = 'PRIMARY'
AND ROWNUM = 1;
SELECT tax.tax_exempt_reason_code
FROM tax_exemptions_qp_v tax,
oe_ship_to_orgs_v s,
hr_organization_information hr,
hz_cust_site_uses site --ra_site_uses (bug fix 2116858)
WHERE tax.ship_to_site_use_id = s.site_use_id
AND s.organization_id = X_Ship_To_Org_Id
AND tax.bill_to_customer_id = hr.org_information1
AND hr.organization_id = X_Invoice_To_Org_Id
and hr.org_information_context = 'Customer/Supplier Association'
and SITE.SITE_USE_ID = TO_NUMBER ( hr.ORG_INFORMATION2 )
and SITE.SITE_USE_CODE = 'BILL_TO'
AND trunc(NVL(X_Date_Ordered, SYSDATE))
between trunc(tax.start_date) and
trunc(NVL(tax.end_date, NVL(X_Date_Ordered, SYSDATE)))
AND tax.status_code = 'PRIMARY'
AND ROWNUM = 1;
SELECT tax_exempt_number,tax_exempt_reason_code
FROM zx_exemptions_v
WHERE site_use_id = NVL(X_Ship_To_Org_Id,X_Invoice_To_Org_id)
AND cust_account_id = X_Sold_To_Org_Id
--* AND tax_code = X_Tax_Code
AND trunc(NVL(X_Tax_Date, SYSDATE)) BETWEEN trunc(effective_from) and
trunc(NVL(effective_to, NVL(X_Tax_Date, SYSDATE)))
AND status_code = 'PRIMARY'
AND rownum = 1;
SELECT cust_acct.cust_account_id,
cust_Acct.party_id,
acct_site.party_site_id,
site_use.org_id
FROM
HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCT_SITES_ALL acct_site,
HZ_CUST_ACCOUNTS_ALL cust_Acct
WHERE site_use.site_use_id = p_site_org_id
AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
and acct_site.cust_account_id = cust_acct.cust_account_id;
SELECT EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON_CODE
INTO l_tax_exempt_number,
l_tax_exempt_reason_code
FROM ZX_EXEMPTIONS_V
WHERE
nvl(site_use_id,nvl(ONT_LINE_Def_Hdlr.g_record.ship_to_org_id,
ONT_LINE_Def_Hdlr.g_record.invoice_to_org_id))
= nvl(ONT_LINE_Def_Hdlr.g_record.ship_to_org_id,
ONT_LINE_Def_Hdlr.g_record.Invoice_to_org_id)
AND nvl(cust_account_id, l_bill_to_cust_acct_id) = l_bill_to_cust_acct_id
AND nvl(PARTY_SITE_ID,nvl(l_ship_to_party_site_id, l_bill_to_party_site_id))=
nvl(l_ship_to_party_site_id, l_bill_to_party_site_id)
AND org_id = l_org_id
AND party_id = l_bill_to_party_id
-- AND nvl(LEGAL_ENTITY_ID,-99) IN (nvl(l_legal_entity_id, legal_entity_id), -99)
AND EXEMPTION_STATUS_CODE = 'PRIMARY'
AND TRUNC(NVL(ONT_LINE_Def_Hdlr.g_record.tax_date,sysdate))
BETWEEN TRUNC(EFFECTIVE_FROM)
AND TRUNC(NVL(EFFECTIVE_TO,NVL(ONT_LINE_Def_Hdlr.g_record.tax_date,sysdate)))
AND ROWNUM = 1;
SELECT tax_exempt_flag,
tax_exempt_number,
tax_exempt_reason_code
INTO l_tax_exempt_flag,
l_tax_exempt_number,
l_tax_exempt_reason_code
FROM OE_ORDER_HEADERS_ALL
WHERE header_id = ONT_LINE_Def_Hdlr.g_record.header_id;
SELECT tax_calculation_flag
into l_calculate_tax_flag
FROM RA_CUST_TRX_TYPES
WHERE CUST_TRX_TYPE_ID = l_cust_trx_type_id;
SELECT NVL(cust_type.subsequent_trx_type_id,cust_type.cust_trx_type_id)
INTO l_cust_trx_type_id
FROM ra_cust_trx_types cust_type,ra_customer_trx_all cust_trx
WHERE cust_type.cust_trx_type_id = cust_trx.cust_trx_type_id
AND cust_trx.customer_trx_id = l_commitment_id;
SELECT tax_calculation_flag
into l_calculate_tax_flag
FROM RA_CUST_TRX_TYPES
WHERE CUST_TRX_TYPE_ID = l_cust_trx_type_id;
SELECT tax_calculation_flag
into l_calculate_tax_flag
FROM RA_CUST_TRX_TYPES
WHERE CUST_TRX_TYPE_ID = l_cust_trx_type_id;
SELECT transactional_curr_code
INTO l_trans_curr_code
FROM oe_order_headers
WHERE header_id = ONT_LINE_Def_Hdlr.g_record.header_id;
SELECT /* MOAC_SQL_CHANGE */ customer_trx_id
INTO l_Commitment_id
FROM ra_customer_trx_all ratrx,
ra_cust_trx_types ractt
WHERE ractt.type in ('DEP','GUAR')
AND ratrx.cust_trx_type_id = ractt.cust_trx_type_id
AND ratrx.org_id = ractt.org_id
AND ratrx.bill_to_customer_id = ONT_LINE_Def_Hdlr.g_record.sold_to_org_id
AND ratrx.invoice_currency_code = l_trans_curr_code
AND TRUNC(sysdate) BETWEEN TRUNC(
NVL(ratrx.start_date_commitment, sysdate))
AND TRUNC(NVL( ratrx.end_date_commitment, sysdate ))
AND ratrx.agreement_id = ONT_LINE_Def_Hdlr.g_record.Agreement_id
/* Peformance changes for sql id 14882692 */
AND ratrx.complete_flag = 'Y'
UNION ALL /* Peformance changes for sql id 14882692 */
SELECT /* MOAC_SQL_CHANGE */ customer_trx_id
--INTO l_Commitment_id
FROM ra_customer_trx_all ratrx,
ra_cust_trx_types ractt
WHERE ractt.type in ('DEP','GUAR')
AND ratrx.cust_trx_type_id = ractt.cust_trx_type_id
AND ratrx.org_id = ractt.org_id
AND ratrx.bill_to_customer_id IN (
SELECT cust_account_id
FROM hz_cust_acct_relate
WHERE related_cust_account_id = ONT_LINE_Def_Hdlr.g_record.sold_to_org_id
AND cust_account_id <> ONT_LINE_Def_Hdlr.g_record.sold_to_org_id
AND status = 'A'
AND bill_to_flag = 'Y')
AND ratrx.invoice_currency_code = l_trans_curr_code
AND TRUNC(sysdate) BETWEEN TRUNC(
NVL(ratrx.start_date_commitment, sysdate))
AND TRUNC(NVL( ratrx.end_date_commitment, sysdate ))
AND ratrx.agreement_id = ONT_LINE_Def_Hdlr.g_record.Agreement_id
AND ratrx.complete_flag = 'Y';
SELECT type
INTO l_rule_type
FROM ra_rules
WHERE rule_id= ONT_LINE_Def_Hdlr.g_record.Accounting_Rule_ID;
SELECT accounting_rule_duration
INTO l_accounting_rule_duration
FROM oe_order_headers
WHERE header_id = ONT_LINE_Def_Hdlr.g_record.header_id;
SELECT /* MOAC_SQL_CHANGE */ SITE.SITE_USE_ID
INTO l_site_use_id
FROM HZ_CUST_ACCT_SITES ADDR
,HZ_CUST_SITE_USES_ALL SITE
WHERE ADDR.CUST_ACCOUNT_ID = l_sold_to_org_id
AND ADDR.STATUS = 'A'
AND SITE.CUST_ACCT_SITE_ID = ADDR.CUST_ACCT_SITE_ID
AND ADDR.ORG_ID = SITE.ORG_ID
AND SITE.SITE_USE_CODE = 'SOLD_TO'
AND SITE.PRIMARY_FLAG = 'Y'
AND SITE.STATUS = 'A'
AND ROWNUM = 1;
select org_id
into l_org_id
from oe_order_headers_all
where header_id = l_header_id;
select receipt_method_id
into l_pay_method_id
from oe_payment_types_all
where payment_type_code = l_payment_type_code
AND nvl(org_id, -99) = nvl(l_org_id, -99);
SELECT /* MOAC_SQL_CHANGE */ SITE.SITE_USE_ID
INTO l_site_use_id
FROM HZ_CUST_ACCT_SITES ADDR
,HZ_CUST_SITE_USES_ALL SITE
WHERE ADDR.CUST_ACCOUNT_ID = l_sold_to_org_id
AND ADDR.STATUS = 'A'
AND SITE.CUST_ACCT_SITE_ID = ADDR.CUST_ACCT_SITE_ID
AND SITE.ORG_ID = ADDR.ORG_ID
AND SITE.SITE_USE_CODE = 'DELIVER_TO'
AND SITE.PRIMARY_FLAG = 'Y'
AND SITE.STATUS = 'A'
AND ROWNUM = 1;