APPS.PO_R12_CAT_UPG_DEF_PVT SQL Statements

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

Line: 115

  SELECT PO_SESSION_GT_S.nextval
  INTO l_key
  FROM DUAL;

Line: 199

    p_headers_rec.last_update_date(i)               := sysdate; -- who column

Line: 200

    p_headers_rec.last_updated_by(i)                := FND_GLOBAL.user_id; -- who column

Line: 212

    p_headers_rec.last_update_login(i)              := FND_GLOBAL.login_id; -- who column

Line: 296

    p_headers_rec.program_update_date(i) := sysdate;  -- DATE

Line: 666

    INSERT INTO PO_SESSION_GT(key,
                              num1,
                              char1,
                              num2)
    SELECT p_key,
           l_subscript_array(i),
           vendor.invoice_currency_code,
           vendor.terms_id
    FROM   po_vendors vendor
    WHERE  vendor.vendor_id = p_headers_rec.vendor_id(i)
    AND    p_headers_rec.vendor_id(i) IS NOT NULL
    --AND    p_headers_rec.has_errors(i) = 'N'
    AND    p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create;

Line: 680

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 686

  DELETE FROM PO_SESSION_GT
  WHERE  key = p_key
  RETURNING num1, char1, num2
  BULK COLLECT INTO l_indexes, l_currency_codes, l_terms_ids;

Line: 691

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;

Line: 777

    INSERT INTO PO_SESSION_GT(key, num1, num2, num3)
    SELECT p_key,
           l_subscript_array(i),
           min(vendor_site.vendor_site_id),
           vendor_site.vendor_id
    FROM   po_vendor_sites_all vendor_site
    WHERE  p_headers_rec.vendor_id(i) IS NOT NULL
    AND    p_headers_rec.vendor_site_id(i) IS NULL
    --AND    p_headers_rec.has_errors(i) = 'N'
    AND    p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create
    AND    vendor_site.vendor_id = p_headers_rec.vendor_id(i)
    AND    vendor_site.purchasing_site_flag = 'Y'
    AND    TRUNC(sysdate) < nvl(vendor_site.inactive_date, TRUNC(sysdate + 1))
    AND    NVL(vendor_site.rfq_only_site_flag, 'N') <> 'Y'
    GROUP BY vendor_site.vendor_id
    HAVING count(vendor_site.vendor_site_id) = 1;

Line: 794

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 800

  DELETE FROM PO_SESSION_GT
  WHERE  key = p_key
  RETURNING num1,
            num2
  BULK COLLECT INTO l_indexes, l_vendor_site_ids;

Line: 806

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;

Line: 907

    INSERT INTO PO_SESSION_GT(key,
                              num1,
                              char1,
                              char2,
                              char3,
                              num2,
                              num3,
                              char4,
                              num4,
                              char5,
                              index_char1)
    SELECT p_key,
           l_subscript_array(i),
           vendor_site.fob_lookup_code,
           vendor_site.ship_via_lookup_code,
           vendor_site.freight_terms_lookup_code,
           vendor_site.ship_to_location_id,
           vendor_site.bill_to_location_id,
           vendor_site.invoice_currency_code,
           vendor_site.terms_id,
           vendor_site.pay_on_code,
           vendor_site.shipping_control -- (not present in 11.5.9)
    FROM   po_vendor_sites_all vendor_site
    WHERE  p_headers_rec.vendor_id(i) IS NOT NULL
    AND    p_headers_rec.vendor_site_id(i) IS NOT NULL
    --AND    p_headers_rec.has_errors(i) = 'N'
    AND    p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create
    AND    vendor_site.vendor_site_id = p_headers_rec.vendor_site_id(i)
    AND    vendor_site.purchasing_site_flag = 'Y'
    AND    TRUNC(sysdate) < nvl(vendor_site.inactive_date, TRUNC(sysdate + 1))
    AND    NVL(vendor_site.rfq_only_site_flag, 'N') <> 'Y';

Line: 939

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 945

  DELETE FROM PO_SESSION_GT
  WHERE  key = p_key
  RETURNING num1,
            char1,
            char2,
            char3,
            num2,
            num3,
            char4,
            num4,
            char5,
            index_char1
  BULK COLLECT INTO l_indexes, l_fob_lookup_codes, l_ship_via_lookup_codes,
                    l_freight_terms_luc, l_ship_to_location_ids,
                    l_bill_to_location_ids, l_currency_codes, l_terms_ids,
                    l_pay_on_codes, l_shipping_controls;

Line: 962

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;

Line: 1056

    INSERT INTO PO_SESSION_GT(key,
                              num1,
                              num2,
                              num3)
    SELECT p_key,
           l_subscript_array(i),
           min(pvc.vendor_contact_id),
           pvc.vendor_site_id
    FROM   po_vendor_contacts pvc
    WHERE  p_headers_rec.vendor_id(i) IS NOT NULL
    AND    p_headers_rec.vendor_site_id(i) IS NOT NULL
    AND    p_headers_rec.vendor_contact_id(i) IS NULL
    --AND    p_headers_rec.has_errors(i) = 'N'
    AND    p_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create
    AND    pvc.vendor_site_id = p_headers_rec.vendor_site_id(i)
    AND    TRUNC(sysdate) < nvl(pvc.inactive_date, TRUNC(sysdate + 1))
    GROUP BY pvc.vendor_site_id
    HAVING count(pvc.vendor_contact_id) = 1;

Line: 1075

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 1081

  DELETE FROM po_session_gt
  WHERE  key = p_key
  RETURNING num1, num2
  BULK COLLECT INTO l_indexes, l_contact_ids;

Line: 1086

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;

Line: 1172

    INSERT INTO PO_SESSION_GT(key,
                              num1,
                              num2)
    SELECT p_key,
           l_subscript_array(i),
           POH.agent_id
    FROM   PO_HEADERS_ALL POH
    WHERE  POH.po_header_id = x_headers_rec.cpa_reference(i)
    AND    x_headers_rec.cpa_reference(i) IS NOT NULL
    AND    x_headers_rec.agent_id(i) IS NULL
    --AND    x_headers_rec.has_errors(i) = 'N'
    AND    x_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create
    AND    EXISTS
            (SELECT 'Its a valid buyer'
             FROM PO_BUYERS_VAL_V VALID_BUYER
             WHERE VALID_BUYER.employee_id = POH.agent_id);

Line: 1189

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 1195

  DELETE FROM PO_SESSION_GT
  WHERE  key = p_key
  RETURNING num1, num2
  BULK COLLECT INTO l_indexes, l_agent_ids;

Line: 1200

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;

Line: 1223

    INSERT INTO PO_SESSION_GT(key,
                              num1,
                              num2)
    SELECT p_key,
           l_subscript_array(i),
           SUB_QUERY.agent_id
    FROM   (
            SELECT agent_id
            FROM
             (SELECT POH.agent_id
              FROM PO_HEADERS_ALL POH
              WHERE POH.vendor_id = x_headers_rec.vendor_id(i)
                AND POH.vendor_site_id = x_headers_rec.vendor_site_id(i)
                AND POH.currency_code = x_headers_rec.currency_code(i)
                AND POH.org_id = x_headers_rec.org_id(i)
                AND POH.type_lookup_code IN ('BLANKET', 'CONTRACT', 'STANDARD')
                AND EXISTS
                      (SELECT 'Its a valid buyer'
                       FROM PO_BUYERS_VAL_V VALID_BUYER
                       WHERE VALID_BUYER.employee_id = POH.agent_id)
              ORDER BY
                DECODE(POH.type_lookup_code,
                       'BLANKET',  1,
                       'CONTRACT', 2,
                       'STANDARD', 3) ASC,
                POH.creation_date DESC)
            WHERE rownum = 1
           ) SUB_QUERY
    WHERE  x_headers_rec.agent_id(i) IS NULL
    --AND    x_headers_rec.has_errors(i) = 'N'
    AND    x_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create;

Line: 1255

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 1261

  DELETE FROM PO_SESSION_GT
  WHERE  key = p_key
  RETURNING num1, num2
  BULK COLLECT INTO l_indexes, l_agent_ids;

Line: 1266

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;

Line: 1289

    INSERT INTO PO_SESSION_GT(key,
                              num1,
                              num2)
    SELECT p_key,
           l_subscript_array(i),
           SUB_QUERY.agent_id
    FROM   (
            SELECT agent_id
            FROM
            (
              SELECT BUYER.agent_id
              FROM PO_AGENTS BUYER, PER_EMPLOYEES_CURRENT_X HRE,
                   HR_ALL_ORGANIZATION_UNITS HROU
              WHERE HRE.EMPLOYEE_ID = BUYER.AGENT_ID
                AND SYSDATE BETWEEN NVL(BUYER.START_DATE_ACTIVE, SYSDATE-1)
                                AND NVL(BUYER.END_DATE_ACTIVE, SYSDATE+1)
                AND HROU.organization_id = x_headers_rec.org_id(i)
                AND HROU.business_group_id = HRE.business_group_id
              ORDER BY BUYER.creation_date DESC
            )
            WHERE rownum = 1
           ) SUB_QUERY
    WHERE  x_headers_rec.agent_id(i) IS NULL
    --AND    x_headers_rec.has_errors(i) = 'N'
    AND    x_headers_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_header_create;

Line: 1315

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 1321

  DELETE FROM PO_SESSION_GT
  WHERE  key = p_key
  RETURNING num1, num2
  BULK COLLECT INTO l_indexes, l_agent_ids;

Line: 1326

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;

Line: 1456

  SELECT PO_SESSION_GT_S.nextval
  INTO l_key
  FROM DUAL;

Line: 1578

    p_lines_rec.last_update_date(i) := sysdate;  -- NOT NULL  DATE  Sysdate

Line: 1580

    p_lines_rec.last_updated_by(i) := FND_GLOBAL.user_id; -- NOT NULL  NUMBER  FND_GLOBAL.user_id

Line: 1584

    p_lines_rec.last_update_login(i) := FND_GLOBAL.login_id;  -- NUMBER  FND_GLOBAL.login_id

Line: 1653

    p_lines_rec.program_update_date(i) := sysdate;  -- DATE

Line: 1864

    INSERT INTO PO_SESSION_GT(key,
                              num1,
                              num2)
    SELECT p_key,
           l_subscript_array(i),
           DECODE(POLTB.purchase_basis,
                  'GOODS', PSP.line_type_id,
                  1)
    FROM PO_SYSTEM_PARAMETERS_ALL PSP,
         PO_LINE_TYPES_B POLTB
    WHERE PSP.org_id = p_hdr_org_ids(i)
    AND   PSP.line_type_id = POLTB.line_type_id
    --AND   x_lines_rec.has_errors(i) = 'N'
    AND   x_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create;

Line: 1879

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 1885

  DELETE FROM po_session_gt
  WHERE  key = p_key
  RETURNING num1, num2
  BULK COLLECT INTO l_indexes, l_line_type_ids;

Line: 1890

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;

Line: 1971

  INSERT INTO po_session_gt(key,
                            num1,
                            char1,
                            char2,
                            char3)
  SELECT p_key,
         l_subscript_array(i),
         order_type_lookup_code,
         purchase_basis,
         matching_basis
  FROM   PO_LINE_TYPES_B
  WHERE  line_type_id = x_lines_rec.line_type_id(i)
   AND   x_lines_rec.line_type_id(i) IS NOT NULL
   --AND   x_lines_rec.has_errors(i) = 'N'
   AND   x_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create;

Line: 1987

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 1993

  DELETE FROM po_session_gt
  WHERE  key = p_key
  RETURNING num1, char1, char2, char3
  BULK COLLECT INTO l_indexes, l_order_type_lookup_codes,
                    l_purchase_basis, l_matching_basis;

Line: 1999

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;

Line: 2079

  INSERT INTO po_session_gt(key,
                            num1,
                            num2,
                            num3,
                            num4,
                            char1)
  SELECT p_key,
         l_subscript_array(i),
         un_number_id,
         hazard_class_id,
         market_price,
         inspection_required_flag
  FROM   MTL_SYSTEM_ITEMS_B
  WHERE  inventory_item_id = x_lines_rec.item_id(i)
   AND   organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id
   AND   x_lines_rec.item_id(i) IS NOT NULL
   --AND   x_lines_rec.has_errors(i) = 'N'
   AND   x_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create;

Line: 2098

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 2104

  DELETE FROM po_session_gt
  WHERE  key = p_key
  RETURNING num1, num2, num3, num4, char1
  BULK COLLECT INTO l_indexes, l_un_number_ids, l_hazard_class_ids,
                    l_market_prices, l_inspection_required_flags;

Line: 2110

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;

Line: 2190

  INSERT INTO po_session_gt(key,
                            num1,
                            num2)
  SELECT p_key,
         l_subscript_array(i),
         inventory_organization_id
  FROM   HR_LOCATIONS_V
  WHERE  location_id = p_location_ids(i)
   AND   ship_to_site_flag = 'Y'
   AND   p_location_ids(i) IS NOT NULL;

Line: 2201

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 2207

  DELETE FROM po_session_gt
  WHERE  key = p_key
  RETURNING num1, num2
  BULK COLLECT INTO l_indexes, l_org_ids;

Line: 2212

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;

Line: 2288

  INSERT INTO po_session_gt(key,
                            num1,
                            num2)
  SELECT p_key,
         l_subscript_array(i),
         hazard_class_id
  FROM   PO_UN_NUMBERS_VAL_V
  WHERE  un_number = x_lines_rec.un_number(i)
   AND   x_lines_rec.un_number(i) IS NOT NULL
   AND   x_lines_rec.hazard_class_id(i) IS NULL
   --AND   x_lines_rec.has_errors(i) = 'N'
   AND   x_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create;

Line: 2301

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 2307

  DELETE FROM po_session_gt
  WHERE  key = p_key
  RETURNING num1, num2
  BULK COLLECT INTO l_indexes, l_hazard_class_ids;

Line: 2312

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;

Line: 2409

  INSERT INTO po_session_gt(key,
                            num1,
                            num2,
                            num3,
                            num4,
                            num5,
                            num6,
                            num7)
  SELECT p_key,
         l_subscript_array(i),
         POH.org_id,
         POH.vendor_id,
         POH.vendor_site_id,
         POH.ship_to_location_id,
         POH.min_release_amount,
         POH.po_header_id
  FROM   PO_HEADERS_ALL POH,
         PO_HEADERS_INTERFACE POHI
  WHERE  POH.po_header_id = POHI.po_header_id
   AND   POHI.interface_header_id = p_lines_rec.interface_header_id(i)
   --AND   p_lines_rec.has_errors(i) = 'N'
   AND   p_lines_rec.action(i) = PO_R12_CAT_UPG_PVT.g_action_line_create;

Line: 2432

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;

Line: 2438

  DELETE FROM po_session_gt
  WHERE  key = p_key
  RETURNING num1, num2, num3, num4, num5, num6, num7
  BULK COLLECT INTO l_indexes, l_org_ids, l_vendor_ids,
                    l_vendor_site_ids, l_ship_to_location_ids,
                    l_min_release_amounts, l_po_header_ids;

Line: 2445

  IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;