The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT RULE_ID
INTO l_id
FROM OE_RA_RULES_V
WHERE NAME = p_accounting_rule;
SELECT AGREEMENT_ID
INTO l_id
FROM OE_AGREEMENTS_V
WHERE NAME = p_agreement;
SELECT CONVERSION_TYPE
INTO l_code
FROM OE_GL_DAILY_CONVERSION_TYPES_V
WHERE USER_CONVERSION_TYPE = p_conversion_type;
SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
INTO l_id
FROM OE_CONTACTS_V CON
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES_ALL SITE
WHERE CON.NAME = p_deliver_to_contact
AND CON.CUSTOMER_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
AND SITE.SITE_USE_ID = p_deliver_to_org_id;
SELECT ORGANIZATION_ID
FROM OE_DELIVER_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_deliver_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_deliver_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_deliver_to_address3, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
nvl( p_deliver_to_address4, fnd_api.g_miss_char)
AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
nvl( p_deliver_to_city, fnd_api.g_miss_char)
AND nvl(STATE,fnd_api.g_miss_char) =
nvl( p_deliver_to_state, fnd_api.g_miss_char)
AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
nvl( p_deliver_to_postal_code, fnd_api.g_miss_char)
AND nvl(COUNTRY,fnd_api.g_miss_char) =
nvl( p_deliver_to_country, fnd_api.g_miss_char)
--AND STATUS = 'A'
AND CUSTOMER_ID = p_sold_to_org_id;
SELECT /*MOAC_SQL_NO_CHANGE*/ ORGANIZATION_ID
FROM OE_DELIVER_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_deliver_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_deliver_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_deliver_to_address3,fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
nvl( p_deliver_to_address4,fnd_api.g_miss_char)
AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
nvl( p_deliver_to_city, fnd_api.g_miss_char)
AND nvl(STATE,fnd_api.g_miss_char) =
nvl( p_deliver_to_state, fnd_api.g_miss_char)
AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
nvl( p_deliver_to_postal_code, fnd_api.g_miss_char)
AND nvl(COUNTRY,fnd_api.g_miss_char) =
nvl( p_deliver_to_country, fnd_api.g_miss_char)
--AND STATUS = 'A'
AND CUSTOMER_ID IN
(
SELECT p_sold_to_org_id FROM DUAL
UNION
SELECT CUST_ACCOUNT_ID FROM
HZ_CUST_ACCT_RELATE WHERE
RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
and ship_to_flag = 'Y');
SELECT ORGANIZATION_ID
FROM OE_DELIVER_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_deliver_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_deliver_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_deliver_to_address3,fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
nvl( p_deliver_to_address4,fnd_api.g_miss_char)
AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
nvl( p_deliver_to_city, fnd_api.g_miss_char)
AND nvl(STATE,fnd_api.g_miss_char) =
nvl( p_deliver_to_state, fnd_api.g_miss_char)
AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
nvl( p_deliver_to_postal_code, fnd_api.g_miss_char)
AND nvl(COUNTRY,fnd_api.g_miss_char) =
nvl( p_deliver_to_country, fnd_api.g_miss_char);
SELECT ORGANIZATION_ID
INTO l_id
FROM OE_DELIVER_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_deliver_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_deliver_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_deliver_to_address3, fnd_api.g_miss_char)
AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
DECODE(STATE, NULL, NULL, STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
DECODE(COUNTRY, NULL, NULL, COUNTRY) =
nvl( p_deliver_to_address4, fnd_api.g_miss_char)
-- AND STATUS = 'A'
AND CUSTOMER_ID = p_sold_to_org_id;
SELECT /*MOAC_SQL_NO_CHANGE*/ ORGANIZATION_ID
INTO l_id
FROM OE_DELIVER_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_deliver_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_deliver_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_deliver_to_address3,fnd_api.g_miss_char)
AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
DECODE(STATE, NULL, NULL, STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
DECODE(COUNTRY, NULL, NULL, COUNTRY) =
NVL( p_deliver_to_address4, fnd_api.g_miss_char)
-- AND STATUS = 'A'
AND CUSTOMER_ID IN
(SELECT p_sold_to_org_id FROM DUAL
UNION
SELECT CUST_ACCOUNT_ID FROM
HZ_CUST_ACCT_RELATE WHERE
RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
and ship_to_flag = 'Y');
oe_debug_pub.add( 'AFTER SELECT FOUND='||L_ID ) ;
SELECT ORGANIZATION_ID
INTO l_id
FROM OE_DELIVER_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_deliver_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_deliver_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_deliver_to_address3,fnd_api.g_miss_char)
AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
DECODE(STATE, NULL, NULL, STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
DECODE(COUNTRY, NULL, NULL, COUNTRY) =
NVL( p_deliver_to_address4, fnd_api.g_miss_char);
SELECT LOOKUP_CODE
INTO l_code
FROM OE_AR_LOOKUPS_V
WHERE MEANING = p_fob_point
AND LOOKUP_TYPE = l_lookup_type;
SELECT LOOKUP_CODE
INTO l_code
FROM OE_LOOKUPS
WHERE MEANING = p_freight_terms
AND LOOKUP_TYPE = l_lookup_type;
SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
INTO l_id
FROM OE_CONTACTS_V CON
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES_ALL SITE
WHERE CON.NAME = p_invoice_to_contact
AND CON.CUSTOMER_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
AND SITE.SITE_USE_ID = p_invoice_to_org_id;
SELECT ORGANIZATION_ID
FROM OE_INVOICE_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_invoice_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_invoice_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_invoice_to_address3,fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
nvl( p_invoice_to_address4,fnd_api.g_miss_char)
AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
nvl( p_invoice_to_city, fnd_api.g_miss_char)
AND nvl(STATE,fnd_api.g_miss_char) =
nvl( p_invoice_to_state, fnd_api.g_miss_char)
AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
nvl( p_invoice_to_postal_code, fnd_api.g_miss_char)
AND nvl(COUNTRY,fnd_api.g_miss_char) =
nvl( p_invoice_to_country, fnd_api.g_miss_char)
AND STATUS = 'A'
AND CUSTOMER_ID = p_sold_to_org_id;
SELECT ORGANIZATION_ID
INTO l_id
FROM OE_INVOICE_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_invoice_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_invoice_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_invoice_to_address3,fnd_api.g_miss_char)
AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
DECODE(STATE, NULL, NULL, STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
DECODE(COUNTRY, NULL, NULL, COUNTRY) =
NVL( p_invoice_to_address4, fnd_api.g_miss_char)
AND STATUS = 'A'
AND CUSTOMER_ID = p_sold_to_org_id;
SELECT ORGANIZATION_ID
FROM OE_INVOICE_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_invoice_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_invoice_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_invoice_to_address3,fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
nvl( p_invoice_to_address4,fnd_api.g_miss_char)
AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
nvl( p_invoice_to_city, fnd_api.g_miss_char)
AND nvl(STATE,fnd_api.g_miss_char) =
nvl( p_invoice_to_state, fnd_api.g_miss_char)
AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
nvl( p_invoice_to_postal_code, fnd_api.g_miss_char)
AND nvl(COUNTRY,fnd_api.g_miss_char) =
nvl( p_invoice_to_country, fnd_api.g_miss_char)
AND STATUS = 'A'
AND CUSTOMER_ID = p_sold_to_org_id;
SELECT /*MOAC_SQL_NO_CHANGE*/ ORGANIZATION_ID
FROM OE_INVOICE_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_invoice_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_invoice_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_invoice_to_address3,fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
nvl( p_invoice_to_address4,fnd_api.g_miss_char)
AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
nvl( p_invoice_to_city, fnd_api.g_miss_char)
AND nvl(STATE,fnd_api.g_miss_char) =
nvl( p_invoice_to_state, fnd_api.g_miss_char)
AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
nvl( p_invoice_to_postal_code, fnd_api.g_miss_char)
AND nvl(COUNTRY,fnd_api.g_miss_char) =
nvl( p_invoice_to_country, fnd_api.g_miss_char)
AND STATUS = 'A'
AND CUSTOMER_ID IN
(
SELECT p_sold_to_org_id FROM DUAL
UNION
SELECT CUST_ACCOUNT_ID FROM
HZ_CUST_ACCT_RELATE WHERE
RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
and bill_to_flag = 'Y');
SELECT ORGANIZATION_ID
FROM OE_INVOICE_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_invoice_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_invoice_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_invoice_to_address3,fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
nvl( p_invoice_to_address4,fnd_api.g_miss_char)
AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
nvl( p_invoice_to_city, fnd_api.g_miss_char)
AND nvl(STATE,fnd_api.g_miss_char) =
nvl( p_invoice_to_state, fnd_api.g_miss_char)
AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
nvl( p_invoice_to_postal_code, fnd_api.g_miss_char)
AND nvl(COUNTRY,fnd_api.g_miss_char) =
nvl( p_invoice_to_country, fnd_api.g_miss_char)
AND STATUS = 'A';
SELECT ORGANIZATION_ID
INTO l_id
FROM OE_INVOICE_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_invoice_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_invoice_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_invoice_to_address3,fnd_api.g_miss_char)
AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
DECODE(STATE, NULL, NULL, STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
DECODE(COUNTRY, NULL, NULL, COUNTRY) =
NVL( p_invoice_to_address4, fnd_api.g_miss_char)
AND STATUS = 'A'
AND CUSTOMER_ID = p_sold_to_org_id;
SELECT /*MOAC_SQL_NO_CHANGE*/ ORGANIZATION_ID
INTO l_id
FROM OE_INVOICE_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_invoice_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_invoice_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_invoice_to_address3,fnd_api.g_miss_char)
AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
DECODE(STATE, NULL, NULL, STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
DECODE(COUNTRY, NULL, NULL, COUNTRY) =
NVL( p_invoice_to_address4, fnd_api.g_miss_char)
AND STATUS = 'A'
AND CUSTOMER_ID IN
(SELECT p_sold_to_org_id FROM DUAL
UNION
SELECT CUST_ACCOUNT_ID FROM
HZ_CUST_ACCT_RELATE WHERE
RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
and bill_to_flag = 'Y');
SELECT ORGANIZATION_ID
INTO l_id
FROM OE_INVOICE_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_invoice_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_invoice_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_invoice_to_address3,fnd_api.g_miss_char)
AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
DECODE(STATE, NULL, NULL, STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
DECODE(COUNTRY, NULL, NULL, COUNTRY) =
NVL( p_invoice_to_address4, fnd_api.g_miss_char)
AND STATUS = 'A';
SELECT RULE_ID
INTO l_id
FROM OE_RA_RULES_V
WHERE NAME = p_invoicing_rule;
SELECT ORDER_SOURCE_ID
INTO l_id
FROM OE_ORDER_SOURCES
WHERE NAME = p_order_source;
SELECT ORDER_TYPE_ID
INTO l_id
FROM OE_ORDER_TYPES_v
WHERE NAME = p_order_type;
SELECT TERM_ID
INTO l_id
FROM OE_RA_TERMS_V
WHERE NAME = p_payment_term;
SELECT LIST_HEADER_ID
INTO l_id
FROM qp_list_headers_vl
WHERE NAME = p_price_list
AND list_type_code='PRL';
SELECT LOOKUP_CODE
INTO l_code
FROM OE_LOOKUPS
WHERE MEANING = p_shipment_priority
AND LOOKUP_TYPE = l_lookup_type;
SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
INTO l_id
FROM OE_CONTACTS_V CON
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES_ALL SITE
WHERE CON.NAME = p_ship_to_contact
AND CON.CUSTOMER_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
AND SITE.SITE_USE_ID = p_ship_to_org_id;
SELECT organization_id
INTO l_id
FROM org_organization_definitions
WHERE organization_name = p_inventory_org;
SELECT ORGANIZATION_ID
FROM OE_SHIP_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_ship_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_ship_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_ship_to_address3, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
nvl( p_ship_to_address4, fnd_api.g_miss_char)
AND nvl( town_or_city, fnd_api.g_miss_char) =
nvl( p_ship_to_city, fnd_api.g_miss_char)
AND nvl( state, fnd_api.g_miss_char) =
nvl( p_ship_to_state, fnd_api.g_miss_char)
AND nvl( postal_code, fnd_api.g_miss_char) =
nvl( p_ship_to_postal_code, fnd_api.g_miss_char)
AND nvl( country, fnd_api.g_miss_char) =
nvl( p_ship_to_country, fnd_api.g_miss_char)
AND STATUS = 'A'
AND CUSTOMER_ID = p_sold_to_org_id;
SELECT ORGANIZATION_ID
INTO l_id
FROM OE_SHIP_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_ship_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_ship_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_ship_to_address3, fnd_api.g_miss_char)
AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
DECODE(STATE, NULL, NULL, STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
DECODE(COUNTRY, NULL, NULL, COUNTRY) =
nvl( p_ship_to_address4, fnd_api.g_miss_char)
AND STATUS = 'A'
AND CUSTOMER_ID = p_sold_to_org_id;
SELECT ORGANIZATION_ID
FROM OE_SHIP_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_ship_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_ship_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_ship_to_address3, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
nvl( p_ship_to_address4, fnd_api.g_miss_char)
AND nvl( town_or_city, fnd_api.g_miss_char) =
nvl( p_ship_to_city, fnd_api.g_miss_char)
AND nvl( state, fnd_api.g_miss_char) =
nvl( p_ship_to_state, fnd_api.g_miss_char)
AND nvl( postal_code, fnd_api.g_miss_char) =
nvl( p_ship_to_postal_code, fnd_api.g_miss_char)
AND nvl( country, fnd_api.g_miss_char) =
nvl( p_ship_to_country, fnd_api.g_miss_char)
AND STATUS = 'A'
AND CUSTOMER_ID = p_sold_to_org_id;
SELECT /*MOAC_SQL_NO_CHANGE*/ ORGANIZATION_ID
FROM OE_SHIP_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_ship_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_ship_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_ship_to_address3, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
nvl( p_ship_to_address4, fnd_api.g_miss_char)
AND nvl( town_or_city, fnd_api.g_miss_char) =
nvl( p_ship_to_city, fnd_api.g_miss_char)
AND nvl( state, fnd_api.g_miss_char) =
nvl( p_ship_to_state, fnd_api.g_miss_char)
AND nvl( postal_code, fnd_api.g_miss_char) =
nvl( p_ship_to_postal_code, fnd_api.g_miss_char)
AND nvl( country, fnd_api.g_miss_char) =
nvl( p_ship_to_country, fnd_api.g_miss_char)
AND STATUS = 'A'
AND CUSTOMER_ID in(
SELECT p_sold_to_org_id FROM DUAL
UNION
SELECT CUST_ACCOUNT_ID FROM
HZ_CUST_ACCT_RELATE WHERE
RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
and ship_to_flag = 'Y');
SELECT ORGANIZATION_ID
FROM OE_SHIP_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_ship_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_ship_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_ship_to_address3, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
nvl( p_ship_to_address4, fnd_api.g_miss_char)
AND nvl( town_or_city, fnd_api.g_miss_char) =
nvl( p_ship_to_city, fnd_api.g_miss_char)
AND nvl( state, fnd_api.g_miss_char) =
nvl( p_ship_to_state, fnd_api.g_miss_char)
AND nvl( postal_code, fnd_api.g_miss_char) =
nvl( p_ship_to_postal_code, fnd_api.g_miss_char)
AND nvl( country, fnd_api.g_miss_char) =
nvl( p_ship_to_country, fnd_api.g_miss_char)
AND STATUS = 'A';
SELECT ORGANIZATION_ID
INTO l_id
FROM OE_SHIP_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_ship_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_ship_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_ship_to_address3, fnd_api.g_miss_char)
AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
DECODE(STATE, NULL, NULL, STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
DECODE(COUNTRY, NULL, NULL, COUNTRY) =
nvl( p_ship_to_address4, fnd_api.g_miss_char)
AND STATUS = 'A'
AND CUSTOMER_ID = p_sold_to_org_id;
SELECT /*MOAC_SQL_NO_CHANGE*/ ORGANIZATION_ID
INTO l_id
FROM OE_SHIP_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_ship_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_ship_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_ship_to_address3, fnd_api.g_miss_char)
AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
DECODE(STATE, NULL, NULL, STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
DECODE(COUNTRY, NULL, NULL, COUNTRY) =
nvl( p_ship_to_address4, fnd_api.g_miss_char)
AND STATUS = 'A'
AND CUSTOMER_ID
in (
SELECT p_sold_to_org_id FROM DUAL
UNION
SELECT CUST_ACCOUNT_ID FROM
HZ_CUST_ACCT_RELATE WHERE
RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
and ship_to_flag = 'Y');
SELECT ORGANIZATION_ID
INTO l_id
FROM OE_SHIP_TO_ORGS_V
WHERE ADDRESS_LINE_1 = p_ship_to_address1
AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
nvl( p_ship_to_address2, fnd_api.g_miss_char)
AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
nvl( p_ship_to_address3, fnd_api.g_miss_char)
AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
DECODE(STATE, NULL, NULL, STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
DECODE(COUNTRY, NULL, NULL, COUNTRY) =
nvl( p_ship_to_address4, fnd_api.g_miss_char)
AND STATUS = 'A';
SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
INTO l_id
FROM OE_CONTACTS_V CON
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES_ALL SITE
WHERE CON.NAME = p_sold_to_contact
AND CON.CUSTOMER_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
AND SITE.SITE_USE_ID = p_sold_to_org_id;
SELECT ORGANIZATION_ID
INTO l_id
FROM OE_SOLD_TO_ORGS_V
WHERE CUSTOMER_NUMBER = p_customer_number;
/* SELECT ORGANIZATION_ID
INTO l_id
FROM OE_SOLD_TO_ORGS_V
WHERE NAME = p_sold_to_org;*/
Select Cust_Acct.Cust_account_id into l_id from HZ_CUST_ACCOUNTS Cust_Acct,
HZ_PARTIES Party where Cust_Acct.Party_id = Party.party_id and
Party.Party_name = p_sold_to_org;
SELECT LOOKUP_CODE
INTO l_code
FROM fnd_lookups
WHERE MEANING = p_tax_exempt
AND LOOKUP_TYPE = l_lookup_type;
SELECT LOOKUP_CODE
INTO l_code
FROM fnd_lookups
WHERE MEANING = p_tax_exempt_reason
AND LOOKUP_TYPE = l_lookup_type;
SELECT LOOKUP_CODE
INTO l_code
FROM OE_AR_LOOKUPS_V
WHERE MEANING = p_tax_point
AND LOOKUP_TYPE = l_lookup_type;
SELECT sales_credit_type_id
INTO l_id
FROM oe_sales_credit_types
WHERE Name = p_sales_credit_type;
SELECT SALESREP_ID
INTO l_id
FROM RA_SALESREPS
WHERE NAME = p_salesrep;
SELECT inventory_item_id
INTO l_id
FROM mtl_system_items_vl
WHERE concatenated_segments =p_inventory_item
AND customer_order_enabled_flag = 'Y'
AND bom_item_type in (1,2,4)
AND organization_id = OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
SELECT LINE_TYPE_ID
INTO l_id
FROM OE_LINE_TYPES_V
WHERE NAME = p_line_type;
SELECT LOOKUP_CODE
INTO l_code
FROM OE_AR_LOOKUPS_V
WHERE MEANING = p_return_reason
AND LOOKUP_TYPE = l_lookup_type;
SELECT LOOKUP_CODE
INTO l_code
FROM OE_LOOKUPS
WHERE MEANING = p_payment_type
AND LOOKUP_TYPE = l_lookup_type;
SELECT LOOKUP_CODE
INTO l_code
FROM OE_LOOKUPS
WHERE MEANING = p_credit_card
AND LOOKUP_TYPE = l_lookup_type;
SELECT lookup_code
INTO l_code
FROM oe_ship_methods_v
WHERE meaning = p_Ship_Method and
rownum = 1 ;
SELECT distinct LOOKUP_CODE
INTO l_code
FROM OE_LOOKUPS
WHERE (lookup_type = 'FLOW_STATUS'
OR lookup_type = 'LINE_FLOW_STATUS')
AND meaning = p_flow_status;
SELECT freight_code
INTO l_code
FROM ORG_FREIGHT
WHERE DESCRIPTION = p_freight_carrier
AND ORGANIZATION_ID = nvl(l_ship_from_org_id,Organization_id);
SELECT LOOKUP_CODE
INTO l_code
FROM OE_LOOKUPS
WHERE LOOKUP_TYPE = l_lookup_type
AND MEANING = p_sales_channel;
SELECT /* MOAC_SQL_CHANGE */ SITE.SITE_USE_ID
INTO l_id
FROM
HZ_CUST_SITE_USES_ALL SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE
SITE.SITE_USE_CODE = 'SOLD_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 PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
AND SITE.ORG_ID = ACCT_SITE.ORG_ID
AND LOC.ADDRESS1 = p_sold_to_location_address1
AND nvl( LOC.ADDRESS2, fnd_api.g_miss_char) =
nvl( p_sold_to_location_address2, fnd_api.g_miss_char)
AND nvl( LOC.ADDRESS3, fnd_api.g_miss_char) =
nvl( p_sold_to_location_address3, fnd_api.g_miss_char)
AND nvl( LOC.ADDRESS4, fnd_api.g_miss_char) =
nvl( p_sold_to_location_address4, fnd_api.g_miss_char)
AND nvl( LOC.city, fnd_api.g_miss_char) =
nvl( p_sold_to_location_city, fnd_api.g_miss_char)
AND nvl( LOC.state, fnd_api.g_miss_char) =
nvl( p_sold_to_location_state, fnd_api.g_miss_char)
AND nvl( LOC.postal_code, fnd_api.g_miss_char) =
nvl( p_sold_to_location_postal, fnd_api.g_miss_char)
AND nvl( LOC.country, fnd_api.g_miss_char) =
nvl( p_sold_to_location_country, fnd_api.g_miss_char)
AND ACCT_SITE.CUST_ACCOUNT_ID = p_sold_to_org_id ;
SELECT ORGANIZATION_ID
INTO l_id
FROM OE_SOLD_TO_ORGS_V
WHERE CUSTOMER_NUMBER = p_end_customer_number;
Select Cust_Acct.Cust_account_id
into l_id
from HZ_CUST_ACCOUNTS Cust_Acct,
HZ_PARTIES Party
where Cust_Acct.Party_id = Party.party_id
and Party.Party_name = p_end_customer;
SELECT CONTACT_ID
INTO l_id
FROM OE_CONTACTS_V
WHERE NAME = p_end_customer_contact
AND CUSTOMER_ID = p_end_customer_id;
SELECT site_use.site_use_id
FROM hz_locations loc,
hz_party_sites site,
hz_cust_acct_sites acct_site,
hz_cust_site_uses site_use
WHERE
site_use.cust_acct_site_id=acct_site.cust_acct_site_id
and acct_site.party_site_id=site.party_site_id
and site.location_id=loc.location_id
and site_use.status='A'
and acct_site.status='A' --bug 2752321
and acct_site.cust_account_id=in_end_customer_id
and loc.address1 = p_end_customer_site_address1
and nvl( loc.address2, fnd_api.g_miss_char) =
nvl( p_end_customer_site_address2, fnd_api.g_miss_char)
and nvl( loc.address3, fnd_api.g_miss_char) =
nvl( p_end_customer_site_address3, fnd_api.g_miss_char)
and nvl( loc.address4, fnd_api.g_miss_char) =
nvl( p_end_customer_site_address4, fnd_api.g_miss_char)
and nvl( loc.city, fnd_api.g_miss_char) =
nvl( p_end_customer_site_city, fnd_api.g_miss_char)
and nvl( loc.state, fnd_api.g_miss_char) =
nvl( p_end_customer_site_state, fnd_api.g_miss_char)
and nvl( loc.postal_code, fnd_api.g_miss_char) =
nvl( p_end_customer_site_postalcode, fnd_api.g_miss_char)
and nvl( loc.country, fnd_api.g_miss_char) =
nvl( p_end_customer_site_country, fnd_api.g_miss_char)
and site_use.site_use_code = in_end_customer_site_use_code;
SELECT site_use.site_use_id
FROM hz_locations loc,
hz_party_sites site,
hz_cust_acct_sites acct_site,
hz_cust_site_uses site_use
WHERE loc.ADDRESS1 = p_end_customer_site_address1
AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
nvl( p_end_customer_site_address2, fnd_api.g_miss_char)
AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
nvl( p_end_customer_site_address3, fnd_api.g_miss_char)
AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
nvl( p_end_customer_site_address4, fnd_api.g_miss_char)
AND site_use.status = 'A'
AND acct_site.status ='A' --bug 2752321
AND acct_site.cust_account_id = p_end_customer_id
and site_use.site_use_code=in_end_customer_site_use_code
and site_use.cust_acct_site_id=acct_site.cust_acct_site_id
and site.party_site_id=acct_site.party_site_id
and site.location_id=loc.location_id;
SELECT LOOKUP_CODE
INTO l_code
FROM OE_LOOKUPS
WHERE MEANING = p_ib_owner
AND LOOKUP_TYPE = l_lookup_type1;-- or LOOKUP_TYPE = l_lookup_type1);
SELECT LOOKUP_CODE
INTO l_code
FROM OE_LOOKUPS
WHERE MEANING = p_ib_installed_at_location
AND LOOKUP_TYPE = l_lookup_type1;-- or LOOKUP_TYPE = l_lookup_type1);
SELECT LOOKUP_CODE
INTO l_code
FROM OE_LOOKUPS
WHERE MEANING = p_ib_current_location
AND LOOKUP_TYPE = l_lookup_type1;-- or LOOKUP_TYPE = l_lookup_type1);