APPS.AHL_MEL_CDL_APPROVALS_PVT SQL Statements

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

Line: 12

SELECT  pcn.name,
        hdr.pc_node_id,
        hdr.mel_cdl_type_code,
        hdr.revision,
        hdr.version_number,
        hdr.object_version_number,
        hdr.revision_date
FROM    ahl_mel_cdl_headers hdr, ahl_pc_nodes_b pcn
WHERE   pcn.pc_node_id = hdr.pc_node_id AND
        hdr.mel_cdl_header_id = p_mel_cdl_header_id;

Line: 29

SELECT
        ue.unit_effectivity_id,
        ue.mel_cdl_type_code,
        ue.log_series_code,
        ue.log_series_number,
        mca.ATA_CODE,
        csi.serial_number,
        mtl.concatenated_segments item_number,
        cs.incident_number,
        cs.summary,
        udf.object_version_number
FROM    ahl_unit_deferrals_b udf, ahl_unit_effectivities_b ue,
        cs_incidents_all_vl cs, ahl_mel_cdl_ata_sequences mca, csi_item_instances csi,
        jtf_notes_vl note, mtl_system_items_kfv mtl
WHERE   udf.unit_effectivity_id = ue.unit_effectivity_id
  AND   ue.csi_item_instance_id = csi.instance_id
  AND   ue.cs_incident_id = cs.incident_id
  AND   udf.ata_sequence_id = mca.MEL_CDL_ATA_SEQUENCE_ID
  AND   note.source_object_code(+) = 'AHL_MEL_CDL'
  AND   note.source_object_id(+) = mca.MEL_CDL_ATA_SEQUENCE_ID
  AND   csi.inventory_item_id = mtl.inventory_item_id
  AND   csi.inv_master_organization_id = mtl.organization_id
  AND   udf.unit_deferral_id = p_unit_deferral_id;

Line: 1717

PROCEDURE UPDATE_STATUS
(
    itemtype        IN          VARCHAR2,
    itemkey         IN          VARCHAR2,
    actid           IN          NUMBER,
    funcmode        IN          VARCHAR2,
    resultout       OUT NOCOPY  VARCHAR2
)
IS
    -- Declare local variables
    l_debug_module  CONSTANT    VARCHAR2(100)   := 'ahl.plsql.'||G_PKG_NAME||'.UPDATE_STATUS';

Line: 1745

    SELECT  mel_cdl_header_id,
            revision_date
    FROM    ahl_mel_cdl_headers
    WHERE   pc_node_id = p_pc_node_id AND
            mel_cdl_type_code = p_mel_cdl_type AND
            version_number = p_version_number - 1;

Line: 1822

            aname       => 'UPDATE_GEN_STATUS'
        );

Line: 1826

        UPDATE  ahl_mel_cdl_headers
        SET     status_code = l_next_status,
                object_version_number = l_object_ovn + 1,
                last_update_date = sysdate,
                last_updated_by = fnd_global.user_id,
                last_update_login = fnd_global.login_id
        WHERE   mel_cdl_header_id = l_object_id;

Line: 1849

            UPDATE  ahl_mel_cdl_headers
            SET     expired_date = l_prev_expired_date,
                    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   mel_cdl_header_id = l_prev_mel_cdl_header_id;

Line: 1922

            'UPDATE_STATUS',
            itemtype,
            itemkey,
            actid,
            funcmode,
            l_error_msg
        );

Line: 1936

            'UPDATE_STATUS',
            itemtype,
            itemkey,
            actid,
            'Unexpected Error! '||SUBSTR(SQLERRM,1,240)
        );

Line: 1943

END UPDATE_STATUS;

Line: 2031

        UPDATE  ahl_mel_cdl_headers
        SET     status_code = l_next_status,
                object_version_number = l_object_ovn + 1,
                last_update_date = sysdate,
                last_updated_by = fnd_global.user_id,
                last_update_login = fnd_global.login_id
        WHERE   mel_cdl_header_id = l_object_id;

Line: 2154

      select cs.incident_number incident_number, cit.name name, udf.object_version_number,
           mtl.concatenated_segments item, csi.serial_number, ue.mel_cdl_type_code,
           cs.expected_resolution_date, seq.ata_code, arc.repair_time,
           apn.name node_name, aph.name class_name, cs.summary, ciu.name repair_category,
           (select visit_number from ahl_visit_tasks_b tsk, ahl_visits_b vst where
            vst.visit_id = tsk.visit_id and tsk.unit_effectivity_id =
            ue.unit_effectivity_id and rownum < 2) visit_number
      from cs_incidents_all_vl cs, cs_incident_types_vl cit,
           ahl_unit_effectivities_b ue, ahl_unit_deferrals_b udf,
           csi_item_instances csi, mtl_system_items_kfv mtl,
           ahl_mel_cdl_ata_sequences seq, ahl_repair_categories arc,
           ahl_mel_cdl_headers mch, ahl_pc_headers_b aph, ahl_pc_nodes_b apn,
           cs_incident_urgencies_vl ciu
      where udf.unit_effectivity_id = ue.unit_effectivity_id
        and ue.cs_incident_id = cs.incident_id
        and cs.incident_type_id = cit.incident_type_id
        and ue.csi_item_instance_id = csi.instance_id
        and mtl.inventory_item_id = csi.inventory_item_id
        and mtl.organization_id = csi.inv_master_organization_id
        and udf.ata_sequence_id = seq.MEL_CDL_ATA_SEQUENCE_ID
        and seq.repair_category_id = arc.repair_category_id
        and mch.mel_cdl_header_id = seq.mel_cdl_header_id
        and mch.pc_node_id = apn.pc_node_id
        and apn.pc_header_id = aph.pc_header_id
        and arc.sr_urgency_id = ciu.INCIDENT_URGENCY_ID
        and udf.unit_deferral_id = p_deferral_id;

Line: 3802

PROCEDURE NR_UPDATE_STATUS
(
    itemtype        IN          VARCHAR2,
    itemkey         IN          VARCHAR2,
    actid           IN          NUMBER,
    funcmode        IN          VARCHAR2,
    resultout       OUT NOCOPY  VARCHAR2
)
IS
    -- Declare local variables
    l_debug_module  CONSTANT    VARCHAR2(100)   := 'ahl.plsql.'||G_PKG_NAME||'.NR_UPDATE_STATUS';

Line: 3827

        select  unit_effectivity_id, object_version_number, unit_deferral_type,
                approval_status_code, ata_sequence_id
        from    ahl_unit_deferrals_b
        where   unit_deferral_id = p_unit_deferral_id and
                unit_deferral_type in ('MEL', 'CDL')
        for update of object_version_number;

Line: 3837

       select   unit_effectivity_id, status_code,
                cs_incident_id, MEL_CDL_TYPE_CODE, csi_item_instance_id,
                unit_config_header_id
       from     ahl_unit_effectivities_b
       where    unit_effectivity_id = p_ue_id
         and    object_type = 'SR'
         and    (status_code IS NULL or status_code = 'INIT_DUE')
       for update of object_version_number;

Line: 3859

  SAVEPOINT AHL_NR_UPDATE_STATUS;

Line: 3871

                           aname    => 'UPDATE_GEN_STATUS'
                        );

Line: 3890

     UPDATE AHL_UNIT_DEFERRALS_TL
     SET approver_notes = l_approver_note,
     SOURCE_LANG = userenv('LANG')
     WHERE unit_deferral_id = l_object_id
     AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 4027

       ROLLBACK TO AHL_NR_UPDATE_STATUS;

Line: 4045

                      'NR_UPDATE_STATUS',
                      itemtype,itemkey,l_error_msg);

Line: 4059

      UPDATE AHL_UNIT_DEFERRALS_TL
      SET approver_notes = substrb(l_error_msg,1,4000),
          last_update_date = sysdate,
          last_updated_by = fnd_global.user_id,
          last_update_login = fnd_global.login_id,
          source_lang = userenv('LANG')
      WHERE unit_deferral_id = l_object_id
        AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 4068

      UPDATE AHL_UNIT_DEFERRALS_B
      SET approval_status_code = 'DEFERRAL_REJECTED',
          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 unit_deferral_id = l_object_id;

Line: 4089

      ROLLBACK TO AHL_NR_UPDATE_STATUS;

Line: 4090

      wf_core.context( 'AHL_MEL_CDL_APPROVALS_PVT', 'NR_UPDATE_STATUS', itemtype, itemkey );

Line: 4095

      UPDATE AHL_UNIT_DEFERRALS_TL
      SET approver_notes = substrb(l_error_msg,1,4000),
          last_update_date = sysdate,
          last_updated_by = fnd_global.user_id,
          last_update_login = fnd_global.login_id,
          source_lang = userenv('LANG')
      WHERE unit_deferral_id = l_object_id
        AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 4104

      UPDATE AHL_UNIT_DEFERRALS_B
      SET approval_status_code = 'DEFERRAL_REJECTED',
          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 unit_deferral_id = l_object_id;

Line: 4117

END NR_UPDATE_STATUS;

Line: 4320

        UPDATE AHL_UNIT_DEFERRALS_TL
        SET approver_notes = substrb(l_error_msg,1,4000),
            LAST_UPDATE_DATE = sysdate,
            LAST_UPDATED_BY = fnd_global.user_id,
            LAST_UPDATE_LOGIN = fnd_global.login_id,
            SOURCE_LANG = userenv('LANG')
        WHERE unit_deferral_id = l_object_id
          AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 4329

        UPDATE AHL_UNIT_DEFERRALS_B
        SET approval_status_code = 'DEFERRAL_REJECTED',
          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 unit_deferral_id = l_object_id;

Line: 4355

        UPDATE AHL_UNIT_DEFERRALS_TL
        SET approver_notes = substrb(l_error_msg,1,4000),
            LAST_UPDATE_DATE = sysdate,
            LAST_UPDATED_BY = fnd_global.user_id,
            LAST_UPDATE_LOGIN = fnd_global.login_id,
            SOURCE_LANG = userenv('LANG')
        WHERE unit_deferral_id = l_object_id
          AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 4364

        UPDATE AHL_UNIT_DEFERRALS_B
        SET approval_status_code = 'DEFERRAL_REJECTED',
            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 unit_deferral_id = l_object_id;