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: 78

        SELECT h.header_id
        INTO   l_header_id
        FROM   oe_order_headers_all h,
		oe_order_lines_all l
        WHERE  h.order_source_id         = nvl(p_order_source_id,10)
	AND    h.header_id=l.header_id
        AND    l.source_document_line_id = p_orig_sys_line_ref --bug 9233983
        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
	AND rownum=1; --bug 9233983

Line: 90

        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    NVL(l.shipped_quantity,0) =0
        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: 104

	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: 157

        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: 163

        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: 175

        SELECT h.header_id
        INTO   l_header_id
        FROM   oe_order_headers_all h,
	       oe_order_lines_all l
        WHERE  h.order_source_id         = nvl(p_order_source_id,10)
	AND    h.header_id=l.header_id
	AND    l.source_document_line_id = p_orig_sys_line_ref --bug 9233983
        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
	AND rownum=1; --bug 9233983

Line: 187

        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: 199

	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: 216

	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: 260

        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: 282

        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: 288

        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: 300

        SELECT h.header_id
        INTO   l_header_id
        FROM   oe_order_headers_all h,
	       oe_order_lines_all l
        WHERE  h.order_source_id         = nvl(p_order_source_id,10)
	AND    h.header_id=l.header_id
	AND    l.source_document_line_id = p_orig_sys_line_ref --bug 9233983
        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
	AND ROWNUM =1; --bug 9233983

Line: 333

	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: 382

        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: 388

        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: 399

        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.header_id=l.header_id
        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: 412

	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: 467

        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: 476

        SELECT h.header_id
        INTO   x_header_id
        FROM   oe_order_headers_all h,
	       oe_order_lines_all l
        WHERE  h.order_source_id         = nvl(p_order_source_id,10)
	AND    h.header_id=l.header_id
	AND    l.source_document_line_id       = p_requisition_line_id
        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
	AND rownum=1;--bug 9233983   --added nvl for bug5394855

Line: 489

	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: 499

	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: 506

	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: 552

         SELECT source_document_id
         INTO   x_req_header_id
         FROM   oe_order_headers_all
         WHERE  header_id = p_header_id;

Line: 557

         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: 564

     	 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: 570

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

Line: 606

        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: 624

   SELECT h.header_id
   INTO   l_header_id
   FROM   oe_order_headers_all h,
	  oe_order_lines_all l
   WHERE  h.order_source_id         = p_order_source_id
   AND    h.header_id=l.header_id
   AND    l.source_document_line_id = p_requisition_line_id
   AND    h.orig_sys_document_ref   = p_orig_sys_document_ref
   AND    h.source_document_id      = p_requisition_header_id
   AND rownum=1; --bug 9233983

Line: 674

        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;