APPS.AHL_OSP_ACCOMP_PVT SQL Statements

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

Line: 68

    SELECT serial_number,
           lot_number,
           inventory_item_id,
           last_vld_organization_id,
           inv_master_organization_id
      FROM csi_item_instances
     WHERE instance_id = c_instance_id;

Line: 78

    SELECT MR_HEADER_ID,
          MR_STATUS_CODE,
          EFFECTIVE_FROM,
          EFFECTIVE_TO,
          TITLE
      FROM AHL_MR_HEADERS_VL
    WHERE MR_HEADER_ID = c_mr_header_id;

Line: 228

    SELECT related_ue_id
      FROM AHL_UE_RELATIONSHIPS
START WITH ue_id = p_unit_effectivity_id
       AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
       AND relationship_code = 'PARENT'
  ORDER BY LEVEL DESC;

Line: 237

    SELECT MR_HEADER_ID,
           CSI_ITEM_INSTANCE_ID,
           OBJECT_VERSION_NUMBER
      FROM ahl_unit_effectivities_b
     WHERE UNIT_EFFECTIVITY_ID = c_unit_effectivity_id;

Line: 313

    l_reset_counter_tbl.DELETE;

Line: 333

    l_return_status := AHL_COMPLETIONS_PVT.update_ump(p_unit_effectivity_id  => l_child_ue_ids_tbl(i),
                                                      p_ue_object_version    => l_ue_ovn,
                                                      p_actual_end_date      => p_accomplishment_date,
                                                      p_counter_tbl          => l_counter_tbl,
                                                      p_dml_flag             => 'C',
                                                      x_msg_count            => l_msg_count,
                                                      x_msg_data             => l_msg_data);

Line: 351

    l_counter_tbl.DELETE;

Line: 429

    SELECT osp_order_line_id
      FROM ahl_osp_accomplishments
     WHERE accomplishment_id = c_accomplishment_id
       AND status_code       = 'OPEN'
       AND accomplished_date is not null;

Line: 436

    SELECT 'X'
      FROM ahl_osp_order_lines
     WHERE osp_order_line_id = c_osp_order_line_id;

Line: 441

    SELECT accomplishment_id
      FROM ahl_osp_accomplishments
     WHERE osp_order_line_id = c_osp_order_line_id
       AND status_code       = 'OPEN'
       AND accomplished_date is not null;

Line: 448

    SELECT item_instance_id,
           mr_header_id,
           accomplished_date,
           object_version_number
      FROM ahl_osp_accomplishments
     WHERE accomplishment_id = c_accomplishment_id
       AND status_code       = 'OPEN'
       AND accomplished_date is not null
       FOR UPDATE of object_version_number;

Line: 460

    SELECT OEL.shipped_quantity
      FROM AHL_OSP_ORDER_LINES OSPL, OE_ORDER_LINES_ALL OEL
     WHERE OSPL.OSP_ORDER_LINE_ID = c_osp_order_line_id
       AND OEL.line_id = OSPL.OE_RETURN_LINE_ID;

Line: 466

    SELECT UER.related_ue_id, UEB.CSI_ITEM_INSTANCE_ID, UEB.MR_HEADER_ID, UEB.ACCOMPLISHED_DATE
      FROM AHL_UE_RELATIONSHIPS UER,
           AHL_UNIT_EFFECTIVITIES_B UEB
     WHERE UER.related_ue_id = UEB.UNIT_EFFECTIVITY_ID
START WITH ue_id = c_ue_id
       AND relationship_code = 'PARENT'
CONNECT BY ue_id = PRIOR related_ue_id
       AND relationship_code = 'PARENT'
  ORDER BY LEVEL DESC;

Line: 612

      FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'About to update Accomplishments table for row with accomplishment_id ' || l_accomplishment_ids_tbl(i) ||
                                                           ' : Setting UNIT_EFFECTIVITY_ID as ' || l_unit_effectivity_id);

Line: 616

    UPDATE AHL_OSP_ACCOMPLISHMENTS
       SET UNIT_EFFECTIVITY_ID   = l_unit_effectivity_id,
           STATUS_CODE           = 'ACCOMPLISHED',
           OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
           LAST_UPDATE_DATE      = SYSDATE,
           LAST_UPDATED_BY       = FND_GLOBAL.user_id,
           LAST_UPDATE_LOGIN     = FND_GLOBAL.login_id
     WHERE ACCOMPLISHMENT_ID = l_accomplishment_ids_tbl(i);

Line: 634

        FND_LOG.STRING(l_log_statement, L_FULL_NAME, 'About to insert additional records into AHL_OSP_ACCOMPLISHMENTS.');

Line: 637

        INSERT INTO AHL_OSP_ACCOMPLISHMENTS(ACCOMPLISHMENT_ID,
                                            OBJECT_VERSION_NUMBER,
                                            LAST_UPDATE_DATE,
                                            LAST_UPDATED_BY,
                                            CREATION_DATE,
                                            CREATED_BY,
                                            LAST_UPDATE_LOGIN,
                                            OSP_ORDER_LINE_ID,
                                            ITEM_INSTANCE_ID,
                                            UNIT_EFFECTIVITY_ID,
                                            MR_HEADER_ID,
                                            PLANNING_TYPE_CODE,
                                            STATUS_CODE,
                                            ACCOMPLISHED_DATE)
                                    VALUES (AHL_OSP_ACCOMPLISHMENTS_S.NEXTVAL,
                                            1,
                                            SYSDATE,
                                            FND_GLOBAL.user_id,
                                            SYSDATE,
                                            FND_GLOBAL.user_id,
                                            FND_GLOBAL.login_id,
                                            NVL(p_osp_order_line_id, l_osp_order_line_id),
                                            l_instance_ids_tbl(j),
                                            l_child_ue_ids_tbl(j),
                                            l_mr_ids_tbl(j),
                                            'MANUALLY_PLANNED',
                                            'ACCOMPLISHED',
                                            l_accomp_dates_tbl(j));

Line: 667

    l_child_ue_ids_tbl.DELETE;

Line: 668

    l_instance_ids_tbl.DELETE;

Line: 669

    l_mr_ids_tbl.DELETE;

Line: 670

    l_accomp_dates_tbl.DELETE;

Line: 757

SELECT distinct ITEM_INSTANCE_ID
  FROM AHL_OSP_ACCOMPLISHMENTS
 WHERE osp_order_line_id = p_osp_order_line_id;

Line: 762

SELECT OEL.shipped_quantity
  FROM AHL_OSP_ORDER_LINES OSPL, OE_ORDER_LINES_ALL OEL
 WHERE OSPL.OSP_ORDER_LINE_ID = p_osp_order_line_id
   AND OEL.line_id = OSPL.OE_RETURN_LINE_ID;

Line: 768

SELECT tld.instance_id
  FROM csi_t_transaction_lines tl,
       csi_t_txn_line_details tld,
       ahl_osp_order_lines ospl
 WHERE ospl.osp_order_line_id = p_osp_order_line_id
   AND tl.source_transaction_id = ospl.oe_return_line_id
   AND tl.source_transaction_table = 'OE_ORDER_LINES_ALL'
   AND tld.transaction_line_id = tl.transaction_line_id;

Line: 778

SELECT c_instance_id from DUAL
UNION
SELECT subject_id from csi_ii_relationships
WHERE relationship_type_code = 'COMPONENT-OF'
  AND NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE
  AND NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE
START WITH object_id = c_instance_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE
        AND NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE
connect by object_id = prior subject_id
        AND relationship_type_code = 'COMPONENT-OF'
        AND NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE
        AND NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE;