APPS.OE_VALUE_TO_ID SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 23

    FND_API.g_attr_tbl.DELETE;

Line: 109

    FND_API.g_attr_tbl(I).name     := 'dw_update_advice';

Line: 292

    SELECT RULE_ID
    INTO l_id
    FROM OE_RA_RULES_V
    WHERE NAME = p_accounting_rule;

Line: 345

    SELECT AGREEMENT_ID
    INTO l_id
    FROM OE_AGREEMENTS_V
    WHERE NAME = p_agreement
    AND sysdate between nvl(start_date_active, sysdate) and nvl(end_date_active, sysdate);

Line: 399

        SELECT  CONVERSION_TYPE
        INTO    l_code
        FROM    OE_GL_DAILY_CONVERSION_TYPES_V
        WHERE   USER_CONVERSION_TYPE = p_conversion_type;

Line: 453

    SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
    INTO l_id
    FROM   OE_CONTACTS_V  CON
         , HZ_ROLE_RESPONSIBILITY ROL
         , HZ_CUST_ACCT_SITES   ADDR
         , HZ_CUST_SITE_USES_ALL   SU
    WHERE CON.NAME = p_deliver_to_contact
    AND   CON.CONTACT_ID = ROL.CUST_ACCOUNT_ROLE_ID(+)
    AND   CON.CUSTOMER_ID = ADDR.CUST_ACCOUNT_ID
    AND   ADDR.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
    AND   SU.SITE_USE_ID = p_deliver_to_org_id
    AND   NVL(ROL.RESPONSIBILITY_TYPE, 'DELIVER_TO') IN ('DELIVER_TO','SHIP_TO');

Line: 518

    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 ADDRESS_STATUS ='A' --bug 2752321
      AND CUSTOMER_ID = in_sold_to_org_id;

Line: 540

    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 ADDRESS_STATUS ='A' --bug 2752321
      AND CUSTOMER_ID IN
  (
                    SELECT in_sold_to_org_id FROM DUAL
                    UNION
                    SELECT CUST_ACCOUNT_ID FROM
                    HZ_CUST_ACCT_RELATE WHERE
                    RELATED_CUST_ACCOUNT_ID = in_sold_to_org_id
                        and ship_to_flag = 'Y' and status='A');

Line: 568

    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 ADDRESS_STATUS ='A';--bug 2752321

Line: 655

          SELECT 1
            INTO l_dummy
           FROM hz_cust_acct_relate
          WHERE cust_account_id = l_deliver_to_customer_id
           AND  related_cust_account_id = l_sold_to_org_id
			and ship_to_flag = 'Y' and status='A';

Line: 715

        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 ADDRESS_STATUS ='A' --bug 2752321
         AND CUSTOMER_ID = l_sold_to_org_id;

Line: 758

        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 ADDRESS_STATUS ='A' --bug 2752321
          AND CUSTOMER_ID IN
                    (SELECT l_sold_to_org_id FROM DUAL
                    UNION
                    SELECT CUST_ACCOUNT_ID FROM
                    HZ_CUST_ACCT_RELATE WHERE
                    RELATED_CUST_ACCOUNT_ID = l_sold_to_org_id
                        and ship_to_flag = 'Y' and status='A');

Line: 781

            oe_debug_pub.add(  'AFTER SELECT FOUND='||L_ID ) ;

Line: 802

        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 ADDRESS_STATUS ='A'; --bug 2752321

Line: 896

    SELECT  LOOKUP_CODE
    INTO    l_code
    FROM    OE_AR_LOOKUPS_V
    WHERE   MEANING = p_fob_point
    AND     LOOKUP_TYPE = l_lookup_type;

Line: 951

    SELECT  LOOKUP_CODE
    INTO    l_code
    FROM    OE_LOOKUPS
    WHERE   MEANING = p_freight_terms
    AND     LOOKUP_TYPE = l_lookup_type;

Line: 1129

    SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
    INTO l_id
    FROM   OE_CONTACTS_V  CON
         , HZ_ROLE_RESPONSIBILITY ROL
         , HZ_CUST_ACCT_SITES   ADDR
         , HZ_CUST_SITE_USES_ALL   SU
    WHERE CON.NAME = p_invoice_to_contact
    AND   CON.CONTACT_ID = ROL.CUST_ACCOUNT_ROLE_ID(+)
    AND   CON.CUSTOMER_ID = ADDR.CUST_ACCOUNT_ID
    AND   ADDR.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
    AND   SU.SITE_USE_ID = p_invoice_to_org_id
    AND   NVL(ROL.RESPONSIBILITY_TYPE, l_usage) = l_usage;

Line: 1195

    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 ADDRESS_STATUS ='A' --bug 2752321
      AND CUSTOMER_ID = in_sold_to_org_id;

Line: 1217

    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 ADDRESS_STATUS ='A' --bug 2752321
      AND CUSTOMER_ID IN
  (
                    SELECT in_sold_to_org_id FROM DUAL
                    UNION
                    SELECT CUST_ACCOUNT_ID FROM
                    HZ_CUST_ACCT_RELATE WHERE
                    RELATED_CUST_ACCOUNT_ID = in_sold_to_org_id
                        and bill_to_flag = 'Y' and status='A');

Line: 1245

    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 ADDRESS_STATUS ='A';--bug 2752321

Line: 1333

          SELECT 1
            INTO l_dummy
           FROM hz_cust_acct_relate
          WHERE cust_account_id = l_invoice_to_customer_id
           AND  related_cust_account_id = l_sold_to_org_id and
			bill_to_flag ='Y' and status='A';

Line: 1395

        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 ADDRESS_STATUS ='A' --bug 2752321
          AND CUSTOMER_ID = l_sold_to_org_id;

Line: 1440

        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 ADDRESS_STATUS ='A' --bug 2752321
          AND CUSTOMER_ID IN
                    (SELECT l_sold_to_org_id FROM DUAL
                    UNION
                    SELECT CUST_ACCOUNT_ID FROM
                    HZ_CUST_ACCT_RELATE WHERE
                    RELATED_CUST_ACCOUNT_ID = l_sold_to_org_id
                        and bill_to_flag = 'Y' and status='A');

Line: 1463

            oe_debug_pub.add(  'AFTER SELECT FOUND='||L_ID ) ;

Line: 1485

        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 ADDRESS_STATUS ='A';--bug 2752321

Line: 1577

    SELECT RULE_ID
    INTO l_id
    FROM OE_RA_RULES_V
    WHERE NAME = p_invoicing_rule;

Line: 1630

    SELECT  ORDER_SOURCE_ID
    INTO    l_id
    FROM    OE_ORDER_SOURCES
    WHERE   NAME = p_order_source;

Line: 1688

    SELECT  ORDER_TYPE_ID
    INTO    l_id
    FROM    OE_ORDER_TYPES_v
    WHERE   NAME = p_order_type;

Line: 1741

    SELECT  TERM_ID
    INTO    l_id
    FROM    OE_RA_TERMS_V
    WHERE   NAME = p_payment_term;

Line: 1794

    SELECT  LIST_HEADER_ID
    INTO    l_id
    FROM    qp_list_headers_vl
    WHERE   NAME = p_price_list
       AND  list_type_code in ('PRL', 'AGR');

Line: 1849

    SELECT  LOOKUP_CODE
    INTO    l_code
    FROM    OE_LOOKUPS
    WHERE   MEANING = p_shipment_priority
    AND     LOOKUP_TYPE =l_lookup_type;

Line: 1925

        select distinct organization_id INTO l_id from oe_ship_from_orgs_v
        where organization_code= p_ship_from_org;

Line: 1929

                select distinct organization_id INTO l_id from oe_ship_from_orgs_v
                where name= p_ship_from_org;

Line: 1987

    SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
    INTO l_id
    FROM   OE_CONTACTS_V  CON
         , HZ_ROLE_RESPONSIBILITY ROL
         , HZ_CUST_ACCT_SITES   ADDR
         , HZ_CUST_SITE_USES_ALL   SU
    WHERE CON.NAME = p_ship_to_contact
    AND   CON.CONTACT_ID = ROL.CUST_ACCOUNT_ROLE_ID(+)
    AND   CON.CUSTOMER_ID = ADDR.CUST_ACCOUNT_ID
    AND   ADDR.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
    AND   SU.SITE_USE_ID = p_ship_to_org_id
    AND   NVL(ROL.RESPONSIBILITY_TYPE, l_usage) = l_usage;

Line: 2046

    SELECT  organization_id
    INTO    l_id
    FROM    org_organization_definitions
    WHERE   organization_name = p_inventory_org;

Line: 2105

    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 ADDRESS_STATUS ='A' --bug 2752321
      AND CUSTOMER_ID = in_sold_to_org_id;

Line: 2127

    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 ADDRESS_STATUS ='A' --bug 2752321
      AND CUSTOMER_ID in(
                    SELECT in_sold_to_org_id FROM DUAL
                    UNION
                    SELECT CUST_ACCOUNT_ID FROM
                    HZ_CUST_ACCT_RELATE WHERE
                    RELATED_CUST_ACCOUNT_ID = in_sold_to_org_id
                        and ship_to_flag = 'Y' and status='A');

Line: 2155

    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 ADDRESS_STATUS ='A';

Line: 2244

          SELECT 1
            INTO l_dummy
           FROM hz_cust_acct_relate
          WHERE cust_account_id = l_ship_to_customer_id
           AND  related_cust_account_id = l_sold_to_org_id
		and ship_to_flag='Y' and status='A';

Line: 2305

        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 ADDRESS_STATUS ='A' --bug 2752321
          AND CUSTOMER_ID = l_sold_to_org_id;

Line: 2338

        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 ADDRESS_STATUS ='A' --bug 2752321
          AND CUSTOMER_ID
 in (
                    SELECT l_sold_to_org_id FROM DUAL
                    UNION
                    SELECT CUST_ACCOUNT_ID FROM
                    HZ_CUST_ACCT_RELATE WHERE
                    RELATED_CUST_ACCOUNT_ID = l_sold_to_org_id
                        and ship_to_flag = 'Y' and status='A');

Line: 2376

        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 ADDRESS_STATUS ='A';

Line: 2467

    SELECT CONTACT_ID
    INTO l_id
    FROM OE_CONTACTS_V
    WHERE NAME = p_sold_to_contact
      AND CUSTOMER_ID = p_sold_to_org_id
     AND STATUS ='A'    /*bug 6711184*/
     AND ROWNUM=1;      /*bug 6711184*/

Line: 2536

          oe_debug_pub.add(  'SITE CUSTOMER SELECTING CUST NUM='||P_SITE_CUSTOMER_NUMBER ) ;

Line: 2540

      SELECT ORGANIZATION_ID
      INTO l_id
      FROM OE_SOLD_TO_ORGS_V
      WHERE CUSTOMER_NUMBER = p_site_customer_number
	     AND status='A'; -- added for bug 3651505

Line: 2547

          oe_debug_pub.add(  'AFTER SELECTING SITE_CUSTOMER' ) ;

Line: 2553

          oe_debug_pub.add(  'SITE CUSTOMER SELECTING CUST='||P_SITE_CUSTOMER ) ;

Line: 2557

      /* SELECT ORGANIZATION_ID
      INTO l_id
      FROM OE_SOLD_TO_ORGS_V
      WHERE NAME = p_site_customer;*/

Line: 2562

      SELECT CUST_ACCT.CUST_ACCOUNT_ID INTO l_id
      FROM HZ_PARTIES PARTY,
           HZ_CUST_ACCOUNTS CUST_ACCT
      WHERE CUST_ACCT.PARTY_ID=PARTY.PARTY_ID
      AND PARTY.PARTY_NAME = p_site_customer
	  AND CUST_ACCT.status ='A'; -- added for bug 3651505

Line: 2570

          oe_debug_pub.add(  'AFTER SELECTING SITE_CUSTOMER' ) ;

Line: 2628

      SELECT ORGANIZATION_ID
      INTO l_id
      FROM OE_SOLD_TO_ORGS_V
      WHERE CUSTOMER_NUMBER = p_customer_number --added for 3651505
    AND status = 'A';

Line: 2635

	 /*SELECT ORGANIZATION_ID
      INTO l_id
      FROM OE_SOLD_TO_ORGS_V
      WHERE NAME = p_sold_to_org;*/

Line: 2639

	    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
	     AND cust_acct.status='A'; -- added for 3651505

Line: 2708

    SELECT LOOKUP_CODE
    INTO    l_code
    FROM    FND_LOOKUPS
    WHERE   MEANING = p_tax_exempt
    AND     LOOKUP_TYPE = l_lookup_type;

Line: 2764

    SELECT LOOKUP_CODE
    INTO    l_code
    FROM    FND_LOOKUPS
    WHERE   MEANING = p_tax_exempt_reason
    AND     LOOKUP_TYPE = l_lookup_type;

Line: 2819

    SELECT LOOKUP_CODE
    INTO    l_code
    FROM    OE_AR_LOOKUPS_V
    WHERE   MEANING = p_tax_point
    AND     LOOKUP_TYPE = l_lookup_type;

Line: 2924

      SELECT  sales_credit_type_id
      INTO    l_id
     FROM    oe_sales_credit_types
     WHERE   Name = p_sales_credit_type;

Line: 2977

    SELECT SALESREP_ID
    INTO l_id
    FROM RA_SALESREPS
    WHERE NAME = p_salesrep;

Line: 2990

     SELECT SALESREP_ID
     INTO l_id
     FROM RA_SALESREPS
     WHERE NAME = p_salesrep
     AND  trunc(NVL(start_date_active, sysdate)) <= trunc(sysdate)
     AND  trunc(NVL(end_date_active, sysdate)) >= trunc(sysdate);

Line: 3213

    SELECT  LINE_TYPE_ID
    INTO    l_id
    FROM    OE_LINE_TYPES_V
    WHERE   NAME = p_line_type;

Line: 3475

    SELECT  LOOKUP_CODE
    INTO    l_code
    FROM    OE_AR_LOOKUPS_V
    WHERE   MEANING = p_return_reason
    AND     LOOKUP_TYPE = l_lookup_type;

Line: 3595

          SELECT PAYMENT_TYPE_CODE
          INTO    l_code
          FROM    OE_PAYMENT_TYPES_TL
          WHERE   ORG_ID = l_org_id
          AND LANGUAGE = USERENV('LANG')
          AND NAME = p_payment_type;

Line: 3604

         SELECT PAYMENT_TYPE_CODE
         INTO l_code
         FROM OE_PAYMENT_TYPES_TL
         WHERE NAME = p_payment_type
         AND LANGUAGE = USERENV('LANG')
         AND ORG_ID is null;

Line: 3614

       SELECT LOOKUP_CODE
       INTO    l_code
       FROM    OE_LOOKUPS
       WHERE   MEANING = p_payment_type
       AND     LOOKUP_TYPE =   l_lookup_type;

Line: 3671

    select CARD_ISSUER_CODE into l_code
    from iby_creditcard_issuers_v
    where description = p_credit_card
    and rownum = 1;

Line: 3724

    SELECT customer_trx_id
    INTO   l_id
    FROM   ra_customer_trx
    WHERE  trx_number = p_commitment;

Line: 4259

      SELECT  lookup_code
      INTO    l_code
      FROM    oe_ship_methods_v
      WHERE   meaning = p_Ship_Method and
	 rownum = 1 ;

Line: 5958

      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;

Line: 6062

      SELECT  LOOKUP_CODE
      INTO    l_code
      FROM    OE_LOOKUPS
      WHERE   LOOKUP_TYPE = l_lookup_type
        AND   MEANING = p_sales_channel;

Line: 6133

    SELECT /* MOAC_SQL_CHANGE */  SITE.SITE_USE_ID
    INTO l_id
    FROM
                HZ_CUST_SITE_USES       SITE,
                HZ_PARTY_SITES          PARTY_SITE,
                HZ_LOCATIONS            LOC,
                HZ_CUST_ACCT_SITES_ALL      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   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_code, 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 SITE.STATUS = 'A'
      AND ACCT_SITE.STATUS = 'A'
      and acct_site.org_id=site.org_id
      AND ACCT_SITE.CUST_ACCOUNT_ID = p_sold_to_org_id;

Line: 6214

      SELECT  LOOKUP_CODE
      INTO    l_code
      FROM    OE_LOOKUPS
      WHERE   LOOKUP_TYPE = l_lookup_type
        AND   MEANING = p_payment_collection_event;

Line: 6262

      SELECT  receipt_method_id
      INTO    l_id
      FROM    AR_RECEIPT_METHODS
      WHERE   NAME = p_receipt_method;

Line: 6320

      SELECT ORGANIZATION_ID
      INTO l_id
      FROM OE_SOLD_TO_ORGS_V
      WHERE CUSTOMER_NUMBER = p_end_customer_number;

Line: 6325

       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;

Line: 6381

   SELECT CONTACT_ID
      INTO l_id
      FROM OE_CONTACTS_V
      WHERE NAME = p_end_customer_contact
      AND CUSTOMER_ID = p_end_customer_id;

Line: 6437

      SELECT /* MOAC_SQL_CHANGE */ site_use.site_use_id
      FROM hz_locations loc,
      hz_party_sites site,
      hz_cust_acct_sites_all 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 acct_site.org_id=site_use.org_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;

Line: 6468

	 SELECT /* MOAC_SQL_CHANGE */ site_use.site_use_id
	 FROM hz_locations loc,
	 hz_party_sites site,
	 hz_cust_acct_sites_all 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
	 and acct_site.org_id=site_use.org_id;

Line: 6691

    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);

Line: 6746

    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);

Line: 6801

    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);

Line: 6864

    SELECT  organization_id
    INTO    l_org_id
    FROM    hr_operating_units
    WHERE   NAME = p_operating_unit;

Line: 6911

    SELECT ID
      INTO l_bill_profile_id
      from oks_billing_profiles_v
     where Profile_number = p_service_bill_profile;

Line: 6946

	SELECT LOOKUP_CODE
	  INTO l_bill_option_code
	  FROM OE_LOOKUPS
	 WHERE lookup_type='OM_BILLING_OPTION_FOR_SERVICES'
	   ANd enabled_flag='Y'
	   and MEANING = p_service_bill_option;

Line: 6983

	select id
      into l_subs_template_id
	  from oks_coverage_templts_v
	 where item_type = 'SUBSCRIPTION'
	   and name = p_service_subs_template
	   and (sysdate between nvl(start_date, sysdate) and nvl(end_date, sysdate)) ;

Line: 7020

	select id
      into l_cov_template_id
	  from oks_coverage_templts_v
	 where item_type = 'SERVICE'
	   and name = p_service_cov_template
	  and (sysdate between nvl(start_date, sysdate) and nvl(end_date, sysdate)) ;