[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /* MOAC_SQL_NO_CHANGE */ 'Y'
INTO l_duplicate_exists
FROM DUAL
WHERE EXISTS (SELECT 'Y'
FROM OE_ORDER_HEADERS
WHERE HEADER_ID <> p_header_id
AND SOLD_TO_ORG_ID = p_sold_to_org_id
AND CUST_PO_NUMBER = p_cust_po_number )
OR EXISTS (SELECT 'Y'
FROM OE_ORDER_LINES
WHERE HEADER_ID <> p_header_id
AND SOLD_TO_ORG_ID = p_sold_to_org_id
AND CUST_PO_NUMBER = p_cust_po_number );
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 /*MOAC_SQL_NO_CHANGE*/ 'VALID'
FROM oe_ship_to_orgs_v
WHERE site_use_id = p_header_rec.ship_to_org_id
AND customer_id = p_header_rec.sold_to_org_id
AND ROWNUM = 1
UNION ALL
SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID'
FROM oe_ship_to_orgs_v osov
WHERE site_use_id = p_header_rec.ship_to_org_id
AND EXISTS
(SELECT 1 FROM
HZ_CUST_ACCT_RELATE hcar
WHERE hcar.cust_account_id = osov.customer_id AND
hcar.related_cust_account_id = p_header_rec.sold_to_org_id
/* added the following condition to fix the bug 2002486 */
AND hcar.ship_to_flag = 'Y')
AND ROWNUM = 1;
SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID'
FROM oe_invoice_to_orgs_v
WHERE site_use_id = p_header_rec.invoice_to_org_id
AND customer_id = p_header_rec.sold_to_org_id
AND ROWNUM = 1
UNION ALL
SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID'
FROM oe_invoice_to_orgs_v oito
WHERE oito.site_use_id = p_header_rec.invoice_to_org_id
AND EXISTS(
SELECT 1 FROM
HZ_CUST_ACCT_RELATE hcar WHERE
hcar.cust_account_id = oito.customer_id AND
hcar.related_cust_account_id = p_header_rec.sold_to_org_id
AND hcar.bill_to_flag = 'Y')
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM OE_ORDER_TYPES_V
WHERE ORDER_TYPE_ID = p_header_rec.order_type_id
AND ROWNUM = 1;
select list_type_code
into l_list_type_code
from qp_list_headers_vl
where list_header_id = p_header_rec.price_list_id;
select name ,sold_to_org_id , price_list_id
into l_agreement_name,l_sold_to_org,l_price_list_id
from oe_agreements_v
where agreement_id = p_header_rec.agreement_id
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM oe_ship_to_orgs_v
WHERE customer_id = p_header_rec.sold_to_org_id
AND site_use_id = p_header_rec.ship_to_org_id
AND ROWNUM = 1;
/*Select /*MOAC_SQL_NO_CHANGE 'VALID'
Into l_dummy
From oe_ship_to_orgs_v
WHERE site_use_id = p_header_rec.ship_to_org_id
AND
customer_id in (
Select p_header_rec.sold_to_org_id from dual
union
select cust_account_id from
HZ_CUST_ACCT_RELATE
Where related_cust_account_id = p_header_rec.sold_to_org_id
/* added the following condition to fix the bug 2002486
and ship_to_flag = 'Y')*/
/* Replaced ra tables with HZ tables to fix the bug 1888440
and rownum = 1;*/
SELECT 'VALID'
INTO l_dummy
FROM oe_ship_to_orgs_v
WHERE site_use_id = p_header_rec.ship_to_org_id
AND ROWNUM = 1;
('Cr: Yes- After the select',2);
SELECT 'VALID'
INTO l_dummy
FROM oe_deliver_to_orgs_v
WHERE customer_id = p_header_rec.sold_to_org_id
AND site_use_id = p_header_rec.deliver_to_org_id
AND ROWNUM = 1;
SELECT /* MOAC_SQL_CHANGE */ 'VALID'
Into l_dummy
FROM HZ_CUST_SITE_USES_ALL SITE,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE SITE.SITE_USE_ID = p_header_rec.deliver_to_org_id
AND SITE.SITE_USE_CODE ='DELIVER_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID in (
SELECT p_header_rec.sold_to_org_id FROM DUAL
UNION
SELECT CUST_ACCOUNT_ID FROM
HZ_CUST_ACCT_RELATE_ALL R WHERE
R.ORG_ID = ACCT_SITE.ORG_ID
AND R.RELATED_CUST_ACCOUNT_ID = p_header_rec.sold_to_org_id
and R.ship_to_flag = 'Y')
AND ROWNUM = 1;
oe_debug_pub.add('Cr: Yes- After the select',2);
SELECT 'VALID'
INTO l_dummy
FROM HZ_CUST_SITE_USES SITE
WHERE SITE.SITE_USE_ID =p_header_rec.deliver_to_org_id;
Select 'VALID'
Into l_dummy
From oe_invoice_to_orgs_v
Where customer_id = p_header_rec.sold_to_org_id
AND site_use_id = p_header_rec.invoice_to_org_id;
/*Select /*MOAC_SQL_NO_CHANGE 'VALID'
Into l_dummy
From oe_invoice_to_orgs_v
WHERE site_use_id = p_header_rec.invoice_to_org_id
AND
customer_id in (
Select p_header_rec.sold_to_org_id from dual
union
select cust_account_id from
HZ_CUST_ACCT_RELATE where
related_cust_account_id = p_header_rec.sold_to_org_id
/* added the following condition to fix the bug 2002486
and bill_to_flag = 'Y')
and rownum = 1;*/
SELECT 'VALID'
INTO l_dummy
From oe_invoice_to_orgs_v
WHERE site_use_id = p_header_rec.invoice_to_org_id
AND ROWNUM = 1;
SELECT /* MOAC_SQL_CHANGE */ 'VALID'
INTO l_dummy
FROM
HZ_CUST_SITE_USES_ALL SITE,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE
SITE.SITE_USE_ID = p_header_rec.sold_to_site_use_id
AND SITE.SITE_USE_CODE = 'SOLD_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = p_header_rec.sold_to_org_id;
SELECT 'VALID'
INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_header_rec.sold_to_contact_id
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ACCT_ROLE.CUST_ACCOUNT_ID = p_header_rec.sold_to_org_id
AND ROWNUM = 1;
SELECT /* MOAC_SQL_CHANGE */ 'VALID'
INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES_ALL INV
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_header_rec.invoice_to_contact_id
AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = INV.CUST_ACCT_SITE_ID
AND INV.SITE_USE_ID = p_header_rec.invoice_to_org_id
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ROWNUM = 1;
SELECT /* MOAC_SQL_CHANGE */ 'VALID'
INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES_ALL SHIP
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_header_rec.ship_to_contact_id
AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SHIP.CUST_ACCT_SITE_ID
AND SHIP.SITE_USE_ID = p_header_rec.ship_to_org_id
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ROWNUM = 1;
SELECT /* MOAC_SQL_CHANGE */ 'VALID'
INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES_ALL DELIVER
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_header_rec.deliver_to_contact_id
AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = DELIVER.CUST_ACCT_SITE_ID
AND DELIVER.SITE_USE_ID = p_header_rec.deliver_to_org_id
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ROWNUM = 1;
/*SELECT 'VALID'
INTO l_dummy
FROM OE_TAX_EXEMPTIONS_QP_V
WHERE TAX_EXEMPT_NUMBER = p_header_rec.tax_exempt_number
AND TAX_EXEMPT_REASON_CODE=p_header_rec.tax_exempt_reason_code
AND SHIP_TO_ORG_ID = nvl(p_header_rec.ship_to_org_id,
p_header_rec.invoice_to_org_id)
AND BILL_TO_CUSTOMER_ID = p_header_rec.sold_to_org_id
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM ZX_EXEMPTIONS_V
WHERE EXEMPT_CERTIFICATE_NUMBER = p_header_rec.tax_exempt_number
AND EXEMPT_REASON_CODE = p_header_rec.tax_exempt_reason_code
AND nvl(site_use_id,nvl(p_header_rec.ship_to_org_id,
p_header_rec.invoice_to_org_id))
= nvl(p_header_rec.ship_to_org_id,
p_header_rec.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(p_header_rec.request_date,sysdate))
-- BETWEEN TRUNC(EFFECTIVE_FROM)
-- AND TRUNC(NVL(EFFECTIVE_TO,NVL(p_header_rec.request_date,sysdate)))
AND ROWNUM = 1;