APPS.CSD_HVR_BI_PVT SQL Statements

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

Line: 14

    SELECT last_run_date
      INTO l_last_run_date
      FROM csd_fact_details
     WHERE fact_name = p_fact_name;

Line: 143

    DELETE FROM CSD_FACT_DETAILS where fact_name = C_CSD_REPAIR_ORDERS_F;

Line: 145

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Delete successful.');

Line: 155

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting into CSD_REPAIR_ORDERS_F ...');

Line: 157

    INSERT INTO CSD_REPAIR_ORDERS_F
      (repair_line_id,
       inventory_item_id,
       primary_quantity,
       primary_uom_code,
       ro_creation_date,
       date_closed,
       Status,
       created_by,
       creation_date,
       last_update_date,
       last_updated_by,
       last_update_login,
       program_id,
       program_login_id,
       program_application_id,
       request_id)
      SELECT RO.repair_line_id,
             RO.inventory_item_id,
             (RO.quantity * UOM.conversion_rate) primary_quantity,
             UOM.primary_uom_code primary_uom_code,
             RO.creation_date,
             RO.date_closed,
             RO.status,
             l_user_id,
             sysdate,
             sysdate,
             l_user_id,
             l_login_id,
             l_program_id,
             l_program_login_id,
             l_program_application_id,
             l_request_id
        FROM CSD_REPAIRS RO, mtl_uom_conversions_view UOM
       WHERE RO.status = 'C'
         AND RO.repair_mode = 'WIP'
         AND UOM.inventory_item_id = RO.inventory_item_id
         AND UOM.organization_id = RO.inventory_org_id
         AND UOM.uom_code = RO.unit_of_measure;

Line: 197

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');

Line: 199

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting a record into CSD_FACT_DETAILS ...');

Line: 201

    INSERT INTO CSD_FACT_DETAILS
      (fact_name,
       last_run_date,
       created_by,
       creation_date,
       last_update_date,
       last_updated_by,
       last_update_login,
       program_id,
       program_login_id,
       program_application_id,
       request_id)
    VALUES
      (C_CSD_REPAIR_ORDERS_F,
       l_run_date,
       l_user_id,
       sysdate,
       sysdate,
       l_user_id,
       l_login_id,
       l_program_id,
       l_program_login_id,
       l_program_application_id,
       l_request_id);

Line: 226

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');

Line: 284

    USING (SELECT RO.repair_line_id,
                  RO.inventory_item_id,
                  (RO.quantity * UOM.conversion_rate) primary_quantity,
                  UOM.primary_uom_code primary_uom_code,
                  RO.creation_date,
                  RO.date_closed,
                  RO.status
             FROM CSD_REPAIRS RO,
                  mtl_uom_conversions_view UOM
            WHERE
            -- RO.status = 'C' AND
            RO.repair_mode = 'WIP'
        AND UOM.inventory_item_id = RO.inventory_item_id
        AND UOM.organization_id = RO.inventory_org_id
        AND UOM.uom_code = RO.unit_of_measure
        AND RO.last_update_date > l_last_run_date) OLTP
    ON (fact.repair_line_id = OLTP.repair_line_id)
    WHEN MATCHED THEN
      UPDATE
         SET fact.inventory_item_id      = OLTP.inventory_item_id,
             fact.primary_quantity       = OLTP.primary_quantity,
             fact.primary_uom_code       = OLTP.primary_uom_code,
             fact.date_closed            = OLTP.date_closed,
             fact.status                 = OLTP.status,
             fact.last_update_date       = sysdate,
             fact.last_updated_by        = l_user_id,
             fact.last_update_login      = l_login_id,
             fact.program_id             = l_program_id,
             fact.program_login_id       = l_program_login_id,
             fact.program_application_id = l_program_application_id,
             fact.request_id             = l_request_id
    WHEN NOT MATCHED THEN
      INSERT
      VALUES
        (OLTP.repair_line_id,
         OLTP.inventory_item_id,
         OLTP.primary_quantity,
         OLTP.primary_uom_code,
         OLTP.creation_date,
         OLTP.date_closed,
         OLTP.status,
         l_user_id,
         sysdate,
         sysdate,
         l_user_id,
         l_login_id,
         l_program_id,
         l_program_login_id,
         l_program_application_id,
         l_request_id);

Line: 339

    UPDATE CSD_FACT_DETAILS
       SET last_run_date          = l_run_date,
           last_update_date       = sysdate,
           last_updated_by        = l_user_id,
           last_update_login      = l_login_id,
           program_id             = l_program_id,
           program_login_id       = l_program_login_id,
           program_application_id = l_program_application_id,
           request_id             = l_request_id
     WHERE fact_name = C_CSD_REPAIR_ORDERS_F;

Line: 350

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Update complete.');

Line: 403

    DELETE FROM CSD_FACT_DETAILS where fact_name = C_CSD_MTL_CONSUMED_F;

Line: 405

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Delete successful.');

Line: 415

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting into CSD_MTL_CONSUMED_F ...');

Line: 417

    INSERT INTO CSD_MTL_CONSUMED_F
      (repair_line_id,
       inventory_item_id,
       primary_quantity,
       primary_uom_code,
       created_by,
       creation_date,
       last_update_date,
       last_updated_by,
       last_update_login,
       program_id,
       program_login_id,
       program_application_id,
       request_id)
      SELECT RO.repair_line_id,
             mmt.inventory_item_id INVENTORY_ITEM_ID,
             SUM(DECODE(MMT.transaction_type_id,
                             lc_MTL_TXN_TYPE_COMP_ISSUE,
                             ABS(mmt.primary_quantity),
                             lc_MTL_TXN_TYPE_COMP_RETURN,
                             (-1 * ABS(mmt.primary_quantity)))) QUANTITY,
             MSI.primary_uom_code UOM,
             l_user_id,
             sysdate,
             sysdate,
             l_user_id,
             l_login_id,
             l_program_id,
             l_program_login_id,
             l_program_application_id,
             l_request_id
        FROM CSD_REPAIR_ORDERS_F       RO,
             CSD_REPAIR_JOB_XREF       XREF,
             WIP_DISCRETE_JOBS         DJOB,
             MTL_MATERIAL_TRANSACTIONS MMT,
             MTL_SYSTEM_ITEMS_B        MSI
       WHERE RO.status = 'C'
         AND XREF.repair_line_id = RO.repair_line_id
         AND XREF.inventory_item_id = RO.inventory_item_id
         AND DJOB.wip_entity_id = XREF.wip_entity_id
         AND DJOB.status_type in (4, 5, 12)
         AND MMT.transaction_source_id = DJOB.wip_entity_id
         AND MMT.transaction_source_type_id = 5 -- 'WIP'
         AND (MMT.transaction_type_id = lc_MTL_TXN_TYPE_COMP_ISSUE
              OR
              MMT.transaction_type_id = lc_MTL_TXN_TYPE_COMP_RETURN)
         AND MMT.inventory_item_id <> RO.inventory_item_id
         AND MSI.inventory_item_id = MMT.inventory_item_id
         AND MSI.organization_id = XREF.organization_id
       GROUP BY RO.repair_line_id,
                MMT.inventory_item_id,
                MSI.primary_uom_code;

Line: 470

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');

Line: 472

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting a record into CSD_FACT_DETAILS ...');

Line: 474

    INSERT INTO CSD_FACT_DETAILS
      (fact_name,
       last_run_date,
       created_by,
       creation_date,
       last_update_date,
       last_updated_by,
       last_update_login,
       program_id,
       program_login_id,
       program_application_id,
       request_id)
    VALUES
      (C_CSD_MTL_CONSUMED_F,
       l_run_date,
       l_user_id,
       sysdate,
       sysdate,
       l_user_id,
       l_login_id,
       l_program_id,
       l_program_login_id,
       l_program_application_id,
       l_request_id);

Line: 499

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');

Line: 557

      USING (SELECT RO.repair_line_id,
                    mmt.inventory_item_id INVENTORY_ITEM_ID,
                    CEIL(SUM(DECODE(MMT.transaction_type_id,
                                    lc_MTL_TXN_TYPE_COMP_ISSUE,
                                    ABS(mmt.primary_quantity),
                                    lc_MTL_TXN_TYPE_COMP_RETURN,
                                    (-1 * ABS(mmt.primary_quantity))))) PRIMARY_QUANTITY,
                    MSI.primary_uom_code PRIMARY_UOM_CODE
               FROM CSD_REPAIR_ORDERS_F       RO,
                    CSD_REPAIR_JOB_XREF       XREF,
                    WIP_DISCRETE_JOBS         DJOB,
                    MTL_MATERIAL_TRANSACTIONS MMT,
                    MTL_SYSTEM_ITEMS_B        MSI
              WHERE RO.status = 'C'
                AND XREF.repair_line_id = RO.repair_line_id
                AND XREF.inventory_item_id = RO.inventory_item_id
                AND DJOB.wip_entity_id = XREF.wip_entity_id
                AND DJOB.status_type in (4, 5, 12)
                AND MMT.transaction_source_id = DJOB.wip_entity_id
                AND MMT.transaction_source_type_id = 5 -- 'WIP'
                AND (MMT.transaction_type_id = lc_MTL_TXN_TYPE_COMP_ISSUE
                     OR
                     MMT.transaction_type_id = lc_MTL_TXN_TYPE_COMP_RETURN)
                AND MMT.inventory_item_id <> RO.inventory_item_id
                AND MSI.inventory_item_id = MMT.inventory_item_id
                AND MSI.organization_id = XREF.organization_id
                AND RO.last_update_date > l_last_run_date
              GROUP BY RO.repair_line_id,
                       MMT.inventory_item_id,
                       MSI.primary_uom_code) OLTP
      ON (fact.repair_line_id = OLTP.repair_line_id AND fact.inventory_item_id = OLTP.inventory_item_id)
      WHEN MATCHED THEN
        UPDATE
           SET fact.primary_quantity       = OLTP.primary_quantity,
               fact.primary_uom_code       = OLTP.primary_uom_code,
               fact.last_update_date       = sysdate,
               fact.last_updated_by        = l_user_id,
               fact.last_update_login      = l_login_id,
               fact.program_id             = l_program_id,
               fact.program_login_id       = l_program_login_id,
               fact.program_application_id = l_program_application_id,
               fact.request_id             = l_request_id
      WHEN NOT MATCHED THEN
        INSERT
        VALUES
          (OLTP.repair_line_id,
           OLTP.inventory_item_id,
           OLTP.primary_quantity,
           OLTP.primary_uom_code,
           l_user_id,
           sysdate,
           sysdate,
           l_user_id,
           l_login_id,
           l_program_id,
           l_program_login_id,
           l_program_application_id,
           l_request_id);

Line: 620

    UPDATE CSD_FACT_DETAILS
       SET last_run_date          = l_run_date,
           last_update_date       = sysdate,
           last_updated_by        = l_user_id,
           last_update_login      = l_login_id,
           program_id             = l_program_id,
           program_login_id       = l_program_login_id,
           program_application_id = l_program_application_id,
           request_id             = l_request_id
     WHERE fact_name = C_CSD_MTL_CONSUMED_F;

Line: 631

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Update complete.');

Line: 684

    DELETE FROM CSD_FACT_DETAILS where fact_name = C_CSD_RES_CONSUMED_F;

Line: 686

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Delete successful.');

Line: 696

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting into CSD_RES_CONSUMED_F ...');

Line: 698

    INSERT INTO CSD_RES_CONSUMED_F
      (repair_line_id,
       resource_id,
       primary_quantity,
       primary_uom_code,
       created_by,
       creation_date,
       last_update_date,
       last_updated_by,
       last_update_login,
       program_id,
       program_login_id,
       program_application_id,
       request_id)
      SELECT RO.repair_line_id,
             WTXN.resource_id resource_id,
             SUM(NVL(WTXN.primary_quantity, 0)) primary_quantity,
             WTXN.primary_uom primary_uom_code,
             l_user_id,
             sysdate,
             sysdate,
             l_user_id,
             l_login_id,
             l_program_id,
             l_program_login_id,
             l_program_application_id,
             l_request_id
        FROM CSD_REPAIR_ORDERS_F RO,
             CSD_REPAIR_JOB_XREF XREF,
             WIP_DISCRETE_JOBS   DJOB,
             WIP_TRANSACTIONS    WTXN
       WHERE RO.status = 'C'
         AND XREF.repair_line_id = RO.repair_line_id
         AND XREF.inventory_item_id = RO.inventory_item_id
         AND DJOB.wip_entity_id = XREF.wip_entity_id
         AND DJOB.status_type in (4, 5, 12)
         AND WTXN.wip_entity_id = DJOB.wip_entity_id
         AND WTXN.transaction_type IN (1, 2, 3)
         AND WTXN.resource_id IS NOT NULL
       GROUP BY RO.repair_line_id, WTXN.primary_uom, WTXN.resource_id;

Line: 739

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');

Line: 741

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Inserting a record into CSD_FACT_DETAILS ...');

Line: 743

    INSERT INTO CSD_FACT_DETAILS
      (fact_name,
       last_run_date,
       created_by,
       creation_date,
       last_update_date,
       last_updated_by,
       last_update_login,
       program_id,
       program_login_id,
       program_application_id,
       request_id)
    VALUES
      (C_CSD_RES_CONSUMED_F,
       l_run_date,
       l_user_id,
       sysdate,
       sysdate,
       l_user_id,
       l_login_id,
       l_program_id,
       l_program_login_id,
       l_program_application_id,
       l_request_id);

Line: 768

    FND_FILE.PUT_LINE(FND_FILE.LOG, lc_proc_name || ': ' || 'Insert successful.');

Line: 826

    USING (SELECT RO.repair_line_id,
                  WTXN.resource_id resource_id,
                  SUM(NVL(WTXN.primary_quantity, 0)) primary_quantity,
                  WTXN.primary_uom primary_uom_code
             FROM CSD_REPAIR_ORDERS_F RO,
                  CSD_REPAIR_JOB_XREF XREF,
                  WIP_DISCRETE_JOBS   DJOB,
                  WIP_TRANSACTIONS    WTXN
            WHERE RO.status = 'C'
              AND XREF.repair_line_id = RO.repair_line_id
              AND XREF.inventory_item_id = RO.inventory_item_id
              AND DJOB.wip_entity_id = XREF.wip_entity_id
              AND DJOB.status_type in (4, 5, 12)
              AND WTXN.wip_entity_id = DJOB.wip_entity_id
              AND WTXN.transaction_type IN (1, 2, 3)
              AND WTXN.resource_id IS NOT NULL
              AND RO.last_update_date > l_last_run_date
            GROUP BY RO.repair_line_id, WTXN.primary_uom, WTXN.resource_id) OLTP
    ON (fact.repair_line_id = OLTP.repair_line_id AND fact.resource_id = OLTP.resource_id)
    WHEN MATCHED THEN
      UPDATE
         SET fact.primary_quantity       = OLTP.primary_quantity,
             fact.primary_uom_code       = OLTP.primary_uom_code,
             fact.last_update_date       = sysdate,
             fact.last_updated_by        = l_user_id,
             fact.last_update_login      = l_login_id,
             fact.program_id             = l_program_id,
             fact.program_login_id       = l_program_login_id,
             fact.program_application_id = l_program_application_id,
             fact.request_id             = l_request_id
    WHEN NOT MATCHED THEN
      INSERT
      VALUES
        (OLTP.repair_line_id,
         OLTP.resource_id,
         OLTP.primary_quantity,
         OLTP.primary_uom_code,
         l_user_id,
         sysdate,
         sysdate,
         l_user_id,
         l_login_id,
         l_program_id,
         l_program_login_id,
         l_program_application_id,
         l_request_id);

Line: 877

    UPDATE CSD_FACT_DETAILS
       SET last_run_date          = l_run_date,
           last_update_date       = sysdate,
           last_updated_by        = l_user_id,
           last_update_login      = l_login_id,
           program_id             = l_program_id,
           program_login_id       = l_program_login_id,
           program_application_id = l_program_application_id,
           request_id             = l_request_id
     WHERE fact_name = C_CSD_RES_CONSUMED_F;

Line: 888

    FND_FILE.PUT_LINE(FND_FILE.LOG,lc_proc_name || ': ' || 'Update complete.');