APPS.OE_ORDER_IMPORT_INTEROP_PUB SQL Statements

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

Line: 59

        SELECT segment1
        INTO   l_orig_sys_document_ref
        FROM   po_requisition_headers_all
        WHERE  requisition_header_id = to_number(p_orig_sys_document_ref);

Line: 65

        SELECT rl.LINE_NUM, nvl(org.operating_unit,-1)
        INTO   l_orig_sys_line_ref, l_org_id
        FROM   po_requisition_lines_all rl,
               org_organization_definitions org
        WHERE  rl.requisition_line_id = to_number(p_orig_sys_line_ref)
        AND    rl.requisition_header_id = to_number(p_orig_sys_document_ref)
        AND    rl.source_organization_id = org.organization_id
        AND    rownum = 1;

Line: 77

        SELECT h.header_id
        INTO   l_header_id
        FROM   oe_order_headers_all h
        WHERE  h.order_source_id         = nvl(p_order_source_id,10)
        AND    h.orig_sys_document_ref   = l_orig_sys_document_ref
        AND    h.source_document_id      = to_number(p_orig_sys_document_ref)
        AND    NVL(h.org_id,-1)          = l_org_id;

Line: 85

        Select sum(nvl(l.ordered_quantity, 0))
        INTO   x_open_qty
        FROM   oe_order_lines_all l
        WHERE  l.header_id               = l_header_id
        AND    l.source_document_id      = to_number(p_orig_sys_document_ref)
        AND    l.source_document_line_id = to_number(p_orig_sys_line_ref)
        AND    l.shipped_quantity       IS NULL
        AND    l.shippable_flag = 'Y'
        AND    nvl(l.cancelled_flag, 'N') = 'N'
        AND    NVL(l.org_id,-1)           = l_org_id
        GROUP BY l.source_document_line_id;

Line: 99

	SELECT nvl(l.ordered_quantity, 0)
  	     - nvl(l.shipped_quantity,0)
	     - nvl(l.cancelled_quantity,0)
	  INTO x_open_qty
	  FROM so_headers h, so_lines l
         WHERE h.original_system_source_code    = p_order_source_id
	   AND h.original_system_reference      = p_orig_sys_document_ref
	   AND h.header_id		        = l.header_id
	   AND l.original_system_line_reference	= p_orig_sys_line_ref
	   AND nvl(l.open_flag,'N')  	        = 'Y';

Line: 152

        SELECT segment1
        INTO l_orig_sys_document_ref
        FROM po_requisition_headers_all
        WHERE requisition_header_id = to_number(p_orig_sys_document_ref);

Line: 158

        SELECT rl.LINE_NUM, nvl(org.operating_unit,-1)
        INTO   l_orig_sys_line_ref, l_org_id
        FROM   po_requisition_lines_all rl,
               org_organization_definitions org
        WHERE  rl.requisition_line_id = to_number(p_orig_sys_line_ref)
        AND    rl.requisition_header_id = to_number(p_orig_sys_document_ref)
        AND    rl.source_organization_id = org.organization_id
        AND    rownum = 1;

Line: 169

        SELECT h.header_id
        INTO   l_header_id
        FROM   oe_order_headers_all h
        WHERE  h.order_source_id         = nvl(p_order_source_id,10)
        AND    h.orig_sys_document_ref   = l_orig_sys_document_ref
        AND    h.source_document_id      = to_number(p_orig_sys_document_ref)
        AND    h.org_id                  = l_org_id;

Line: 177

        Select sum(nvl(l.shipped_quantity,0))
        INTO   x_shipped_qty
        FROM   oe_order_lines_all l
        WHERE  l.header_id               = l_header_id
        AND    l.source_document_id      = to_number(p_orig_sys_document_ref)
        AND    l.source_document_line_id = to_number(p_orig_sys_line_ref)
        AND    l.org_id                  = l_org_id
        GROUP BY l.source_document_line_id;

Line: 189

	SELECT nvl(l.shipped_quantity,0)
	  INTO x_shipped_qty
-- Following is changed to _all tables because of change in PO to multi-org
	  FROM oe_order_headers_all h, oe_order_lines_all l
-- Following is changed to _all tables because of change in PO to multi-org
       WHERE h.order_source_id       	= nvl(p_order_source_id,10)
	   --AND h.source_document_id    	= p_orig_sys_document_ref
           AND h.orig_sys_document_ref        = l_orig_sys_document_ref
-- aksingh adding this for internal order multi-org change duplicate issue
-- Bug 1794206 fix
           AND h.source_document_id     = p_orig_sys_document_ref
	   AND h.header_id		          = l.header_id
	   --AND l.source_document_line_id   = p_orig_sys_line_ref;

Line: 206

	SELECT nvl(l.shipped_quantity,0)
	  INTO x_shipped_qty
	  FROM so_headers h, so_lines l
         WHERE h.original_system_source_code    = p_order_source_id
	   AND h.original_system_reference      = p_orig_sys_document_ref
	   AND h.header_id		        = l.header_id
	   AND l.original_system_line_reference = p_orig_sys_line_ref;

Line: 250

        Select sum(l.cancelled_quantity) cancelled_quantity,
               sum( l.shipped_quantity) shipped_quantity,
               sum(l.ordered_quantity) ordered_quantity
        FROM   oe_order_lines_all l
        WHERE  l.header_id               = l_header_id
        AND    l.source_document_id      = to_number(p_orig_sys_document_ref)
        AND    l.source_document_line_id = to_number(p_orig_sys_line_ref)
        AND    l.org_id                  = l_org_id
        GROUP BY l.source_document_line_id;

Line: 272

        SELECT segment1
        INTO l_orig_sys_document_ref
        FROM po_requisition_headers_all
        WHERE requisition_header_id = to_number(p_orig_sys_document_ref);

Line: 278

        SELECT rl.LINE_NUM, nvl(org.operating_unit,-1)
        INTO   l_orig_sys_line_ref, l_org_id
        FROM   po_requisition_lines_all rl,
               org_organization_definitions org
        WHERE  rl.requisition_line_id = to_number(p_orig_sys_line_ref)
        AND    rl.requisition_header_id = to_number(p_orig_sys_document_ref)
        AND    rl.source_organization_id = org.organization_id
        AND    rownum = 1;

Line: 289

        SELECT h.header_id
        INTO   l_header_id
        FROM   oe_order_headers_all h
        WHERE  h.order_source_id         = nvl(p_order_source_id,10)
        AND    h.orig_sys_document_ref   = l_orig_sys_document_ref
        AND    h.source_document_id      = to_number(p_orig_sys_document_ref)
        AND    h.org_id                  = l_org_id;

Line: 318

	SELECT l.cancelled_quantity
	  INTO x_cancelled_qty
	  FROM so_headers h, so_lines l
         WHERE h.original_system_source_code    = p_order_source_id
	   AND h.original_system_reference 	= p_orig_sys_document_ref
	   AND h.header_id		        = l.header_id
	   AND l.original_system_line_reference = p_orig_sys_line_ref;

Line: 367

        SELECT segment1
        INTO l_orig_sys_document_ref
        FROM po_requisition_headers_all
        WHERE requisition_header_id = to_number(p_orig_sys_document_ref);

Line: 373

        SELECT rl.LINE_NUM, nvl(org.operating_unit,-1)
        INTO   l_orig_sys_line_ref, l_org_id
        FROM   po_requisition_lines_all rl,
               org_organization_definitions org
        WHERE  rl.requisition_line_id = to_number(p_orig_sys_line_ref)
        AND    rl.requisition_header_id = to_number(p_orig_sys_document_ref)
        AND    rl.source_organization_id = org.organization_id
        AND    rownum = 1;

Line: 384

        SELECT distinct h.order_number
        INTO   x_order_number
        FROM   oe_order_headers_all h,oe_order_lines_all l
        WHERE  h.order_source_id         = nvl(p_order_source_id,10)
        AND    h.orig_sys_document_ref   = l_orig_sys_document_ref
        AND    h.source_document_id      = to_number(p_orig_sys_document_ref)
        AND    l.orig_sys_document_ref   = l_orig_sys_document_ref
        AND    l.orig_sys_line_ref       = l_orig_sys_line_ref
        AND    h.source_document_id      = l.source_document_id
        AND    nvl(h.org_id, -1)          = l_org_id;

Line: 396

	SELECT h.order_number
	  INTO x_order_number
	  FROM so_headers h, so_lines l
         WHERE h.original_system_source_code    = p_order_source_id
	   AND h.original_system_reference 	= p_orig_sys_document_ref
	   AND h.header_id		        = l.header_id
	   AND l.original_system_line_reference = p_orig_sys_line_ref;

Line: 451

        SELECT rl.LINE_NUM, nvl(org.operating_unit,-1)
        INTO   l_orig_sys_line_ref, l_org_id
        FROM   po_requisition_lines_all rl,
               org_organization_definitions org
        WHERE  rl.requisition_line_id    = p_requisition_line_id
        AND    rl.requisition_header_id  = p_requisition_header_id
        AND    rl.source_organization_id = org.organization_id
        AND    rownum = 1;

Line: 460

        SELECT h.header_id
        INTO   x_header_id
        FROM   oe_order_headers_all h
        WHERE  h.order_source_id         = nvl(p_order_source_id,10)
        AND    h.orig_sys_document_ref   = p_orig_sys_document_ref
        AND    h.source_document_id      = p_requisition_header_id
        AND    nvl(h.org_id,-1)          = l_org_id;--added nvl for bug5394855

Line: 468

	SELECT max(d.header_id)
	  INTO x_header_id
	  FROM oe_drop_ship_sources d
         WHERE d.requisition_header_id  = p_requisition_header_id
           AND d.requisition_line_id    =
               nvl(p_requisition_line_id, d.requisition_line_id);

Line: 478

	SELECT h.header_id
	  INTO x_header_id
	  FROM so_headers h
         WHERE h.original_system_source_code = p_order_source_id
	   AND h.original_system_reference   = p_orig_sys_document_ref;

Line: 485

	SELECT max(d.header_id)
	  INTO x_header_id
	  FROM so_drop_ship_sources d
         WHERE d.requisition_header_id  = p_requisition_header_id;

Line: 531

         SELECT source_document_id
         INTO   x_req_header_id
         FROM   oe_order_headers_all
         WHERE  header_id = p_header_id;

Line: 536

         SELECT max(d.requisition_header_id)
	 INTO   x_req_header_id
	 FROM   oe_drop_ship_sources d
         WHERE  d.header_id = p_header_id;

Line: 543

     	 SELECT r.requisition_header_id
	 INTO x_req_header_id
	 FROM so_headers h, po_requisition_headers r
         WHERE h.header_id    		   = p_header_id
         AND h.original_system_reference = r.segment1;

Line: 549

         SELECT d.requisition_header_id
	 INTO x_req_header_id
	 FROM so_drop_ship_sources d
         WHERE d.header_id = p_header_id;

Line: 585

        Select line_id
        FROM   oe_order_lines_all l
        WHERE  l.header_id               = l_header_id
        AND    l.source_document_id      = p_requisition_header_id
        AND    l.source_document_line_id = p_requisition_line_id
        AND    l.order_source_id         = p_order_source_id
        AND    l.shipped_quantity       IS NOT NULL;

Line: 600

   SELECT h.header_id
   INTO   l_header_id
   FROM   oe_order_headers_all h
   WHERE  h.order_source_id         = p_order_source_id
   AND    h.orig_sys_document_ref   = p_orig_sys_document_ref
   AND    h.source_document_id      = p_requisition_header_id;

Line: 646

        SELECT distinct d.requisition_header_id
        FROM   oe_drop_ship_sources d
        WHERE  d.header_id = p_header_id
        And    d.requisition_header_id  is not null;