APPS.OE_BULK_PROCESS_HEADER SQL Statements

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

Line: 23

     SELECT 'VALID'
       INTO l_dummy
       FROM hz_cust_acct_relate
      WHERE RELATED_CUST_ACCOUNT_ID = p_related_customer_id
        AND CUST_ACCOUNT_ID = p_customer_id AND STATUS='A'
        AND ROWNUM = 1;

Line: 180

  SELECT 'Y'
  INTO l_dummy
  FROM fnd_lookups l
  WHERE l.LOOKUP_CODE = p_tax_exempt_reason_code --7782998
    AND l.LOOKUP_TYPE = 'ZX_EXEMPTION_REASON_CODE'
    AND l.ENABLED_FLAG = 'Y'
    AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
        AND NVL(END_DATE_ACTIVE, SYSDATE)
    ;

Line: 223

     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;

Line: 259

  SELECT 'VALID'
    INTO l_dummy
    FROM ZX_EXEMPTIONS_V
   WHERE EXEMPT_CERTIFICATE_NUMBER  = p_tax_exempt_number
     AND EXEMPT_REASON_CODE = p_tax_exempt_reason_code
     AND nvl(site_use_id,nvl(p_ship_to_org_id, p_invoice_to_org_id)) =
               nvl(p_ship_to_org_id,p_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_request_date,sysdate))
          BETWEEN TRUNC(EFFECTIVE_FROM)
               AND TRUNC(NVL(EFFECTIVE_TO,NVL(p_request_date,sysdate)))
     AND ROWNUM = 1;

Line: 295

     SELECT  'VALID' INTO l_dummy
       FROM OE_SOLD_TO_ORGS_V
      WHERE   ORGANIZATION_ID =p_end_customer_id    AND
      STATUS = 'A' AND
    SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
	AND NVL(END_DATE_ACTIVE, SYSDATE);

Line: 308

	      OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'END_CUSTOMER_ID');

Line: 314

	      OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);

Line: 344

   SELECT  'VALID'  INTO  l_dummy
     FROM  OE_RA_CONTACTS_V CON
    WHERE   CON.CONTACT_ID = p_end_customer_contact_id
      AND     CON.STATUS = 'A';

Line: 355

	      OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'END_CUSTOMER_CONTACT_ID');

Line: 361

	      OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);

Line: 395

/*   SELECT  'VALID'
	    INTO
	    l_dummy
	    FROM
	    hz_cust_site_uses site_use,
	    hz_cust_acct_sites acct_site
	    WHERE
	    site_use.site_use_id=p_end_customer_site_use_id
	    and site_use.cust_acct_site_id=acct_site.cust_acct_site_id
	    and acct_site.cust_account_id=p_end_customer_id;

Line: 423

	      OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'END_CUSTOMER_SITE_USE_ID');

Line: 429

	      OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);

Line: 460

      SELECT  'VALID'      INTO
       l_dummy      FROM     OE_LOOKUPS
       WHERE    lookup_code = p_ib_owner AND
       ( lookup_type = l_lookup_type1);

Line: 473

	      OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'IB_INSTALLED_AT_LOCATION');

Line: 479

	      OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);

Line: 513

	  SELECT  'VALID'  INTO  l_dummy FROM OE_LOOKUPS
	   WHERE    lookup_code = p_ib_installed_at_location AND
	   (lookup_type = l_lookup_type1);

Line: 525

	      OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'IB_INSTALLED_AT_LOCATION');

Line: 531

	      OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);

Line: 568

      SELECT  'VALID'
	INTO     l_dummy
	FROM     OE_LOOKUPS
       WHERE    lookup_code = p_ib_current_location AND
       (lookup_type = l_lookup_type1 );

Line: 582

	   OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'IB_CURRENT_LOCATION');

Line: 588

	    OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);

Line: 1175

    SELECT freight_code
    INTO   l_freight_code
    FROM   wsh_carriers wsh_ca,wsh_carrier_services wsh,
           wsh_org_carrier_services wsh_org
    WHERE  wsh_org.organization_id   = p_ship_from_org_id
      AND  wsh.carrier_service_id    = wsh_org.carrier_service_id
      AND  wsh_ca.carrier_id         = wsh.carrier_id
      AND  wsh.ship_method_code      = p_shipping_method_code
      AND  wsh_org.enabled_flag='Y';

Line: 1186

    SELECT freight_code
    INTO l_freight_code
    FROM wsh_carrier_ship_methods
    WHERE ship_method_code = p_shipping_method_code
      AND ORGANIZATION_ID = p_ship_from_org_id;

Line: 1427

     SELECT 'VALID'
       INTO l_dummy
       FROM HZ_CUST_ACCT_RELATE
      WHERE RELATED_CUST_ACCOUNT_ID = p_sold_to
        AND CUST_ACCOUNT_ID = OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).customer_id
        AND ship_to_flag = 'Y' AND STATUS='A';

Line: 1518

     SELECT 'VALID'
       INTO l_dummy
       FROM HZ_CUST_ACCT_RELATE
      WHERE RELATED_CUST_ACCOUNT_ID = p_sold_to
        AND CUST_ACCOUNT_ID = OE_Bulk_Cache.G_INVOICE_TO_TBL(l_c_index).customer_id
        AND bill_to_flag = 'Y' AND STATUS='A';

Line: 1542

     SELECT /*MOAC_SQL_NO_CHANGE */ 'VALID'
     FROM oe_deliver_to_orgs_v
     WHERE site_use_id = p_deliver_to
     AND    status = 'A'
     AND customer_id = p_sold_to
     AND ROWNUM = 1

     UNION ALL

     SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID'
     FROM oe_deliver_to_orgs_v odto
     WHERE site_use_id = p_deliver_to
     AND    status = 'A'
     AND exists
	  (
	    SELECT 1
	    FROM HZ_CUST_ACCT_RELATE hcar
	    WHERE hcar.CUST_ACCOUNT_ID = odto.customer_id
	    AND hcar.RELATED_CUST_ACCOUNT_ID = p_sold_to
	    AND hcar.ship_to_flag = 'Y'
	    AND hcar.STATUS ='A'
	    )
    AND ROWNUM=1;

Line: 1570

     SELECT 'VALID'
        INTO   l_dummy
        FROM   oe_deliver_to_orgs_v
        WHERE  customer_id = p_sold_to
        AND    site_use_id = p_deliver_to
        AND    status = 'A';

Line: 1579

     /*SELECT /*MOAC_SQL_NO_CHANGE 'VALID'
       INTO l_dummy
       FROM oe_deliver_to_orgs_v
      WHERE site_use_id = p_deliver_to
        AND    status = 'A'
        AND customer_id IN (
                    Select p_sold_to from dual
                    union
                    SELECT CUST_ACCOUNT_ID
                    FROM HZ_CUST_ACCT_RELATE
                    WHERE RELATED_CUST_ACCOUNT_ID = p_sold_to
                      AND ship_to_flag = 'Y' AND STATUS ='A'
                    );*/

Line: 1622

    SELECT  /* MOAC_SQL_CHANGE */ 'VALID'
      INTO  l_dummy
      FROM  HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
            HZ_CUST_SITE_USES_ALL SITE,
            HZ_CUST_ACCT_SITES  ADDR
     WHERE  ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
       AND  ACCT_ROLE.CUST_ACCOUNT_ID = ADDR.CUST_ACCOUNT_ID
       AND  ACCT_ROLE.ROLE_TYPE = 'CONTACT'
       AND  ADDR.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
       AND  SITE.SITE_USE_ID  = p_site_use_id
       AND  SITE.STATUS = 'A'
       AND  ACCT_ROLE.STATUS = 'A'
       AND  ADDR.STATUS ='A' ;-- added for bug 2752321

Line: 1934

           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(i)
             AND  ACCT_ROLE.CUST_ACCOUNT_ID = p_header_rec.sold_to_org_id(i)
             AND  ROWNUM = 1
             AND  ACCT_ROLE.ROLE_TYPE = 'CONTACT'
             AND  STATUS = 'A';

Line: 2807

               UPDATE oe_headers_iface_all
               SET error_flag='Y'
               WHERE order_source_id=p_header_rec.order_source_id(i)
               and orig_sys_document_ref=p_header_rec.orig_sys_document_ref(i);