APPS.CST_COSTPROCVALIDATION_PVT SQL Statements

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

Line: 29

     SELECT DISTINCT organization_id
     FROM   mtl_material_transactions
     WHERE  costed_flag = 'N';

Line: 36

     SELECT mp.organization_code,
            count (transaction_id)
     FROM   mtl_material_transactions mmt,
            mtl_parameters mp
     WHERE  mmt.costed_flag = 'E'
     AND    mmt.last_update_date >= l_start_date
     AND    mp.organization_id = mmt.organization_id
     GROUP BY mp.organization_code;

Line: 50

   l_last_updated_by         NUMBER;

Line: 51

   l_last_update_login       NUMBER;

Line: 66

   l_last_updated_by         := fnd_global.user_id;

Line: 67

   l_last_update_login       := fnd_global.login_id;

Line: 96

   SELECT concurrent_program_id
   INTO   l_cmcmcw_prog_id
   FROM   fnd_concurrent_programs
   WHERE  application_id          = l_application_id
   AND    concurrent_program_name = l_concurrent_program_name;

Line: 105

   SELECT concurrent_program_id
   INTO   l_cmcacw_prog_id
   FROM   fnd_concurrent_programs
   WHERE  application_id          = l_application_id
   AND    concurrent_program_name = l_concurrent_program_name;

Line: 114

   SELECT concurrent_program_id
   INTO   l_cmclcw_prog_id
   FROM   fnd_concurrent_programs
   WHERE  application_id          = l_application_id
   AND    concurrent_program_name = l_concurrent_program_name;

Line: 121

   UPDATE /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
          mtl_material_transactions MMT
   SET    transaction_group_id = NULL,
          last_update_date = SYSDATE,
          last_updated_by = l_last_updated_by,
          last_update_login = l_last_update_login,
          program_update_date = SYSDATE,
          program_application_id = l_program_application_id,
          program_id = l_program_id,
          request_id = l_request_id
   WHERE  MMT.transaction_group_id is not null
   AND NVL(MMT.logical_transaction,0) <> 1
   AND MMT.costed_flag = 'N'
   AND NOT EXISTS
   ( SELECT 'incomplete concurrent program'
     FROM fnd_concurrent_requests FCR
     WHERE FCR.concurrent_program_id in (l_cmcmcw_prog_id, l_cmcacw_prog_id, l_cmclcw_prog_id)
     AND FCR.program_application_id = 702
     AND FCR.phase_code in ('P','R','I')
     AND FCR.argument1 = TO_CHAR(MMT.transaction_group_id));

Line: 147

   UPDATE mtl_material_transactions mmt
   SET    costed_flag = 'E',
          error_code = l_error_code,
          error_explanation = l_error_expl,
          last_update_date = SYSDATE,
          last_updated_by = l_last_updated_by,
          last_update_login = l_last_update_login,
          program_update_date = SYSDATE,
          program_application_id = l_program_application_id,
          program_id = l_program_id,
          request_id = l_request_id
   WHERE  mmt.costed_flag = 'N'
   AND    mmt.transaction_action_id in (3,12,21)
   AND    mmt.organization_id = mmt.transfer_organization_id;

Line: 167

   UPDATE mtl_material_transactions mmt
   SET    costed_flag = 'E',
          error_code = l_error_code,
          error_explanation = l_error_expl,
          last_update_date = SYSDATE,
          last_updated_by = l_last_updated_by,
          last_update_login = l_last_update_login,
          program_update_date = SYSDATE,
          program_application_id = l_program_application_id,
          program_id = l_program_id,
          request_id = l_request_id
   WHERE  mmt.costed_flag = 'N'
   AND    mmt.transaction_action_id IN (2,3,5,28)
   AND  (  mmt.transfer_transaction_id IS NULL
        OR NOT EXISTS (
          SELECT 1 FROM mtl_secondary_inventories msi
          WHERE msi.secondary_inventory_name = mmt.subinventory_code
          AND   msi.organization_id = mmt.organization_id)
        OR NOT EXISTS (
          SELECT 1 FROM mtl_secondary_inventories msi
          WHERE msi.secondary_inventory_name = mmt.transfer_subinventory
          AND   msi.organization_id = mmt.transfer_organization_id)
        OR (    mmt.transaction_action_id IN (2,5,28)
            AND mmt.organization_id <> mmt.transfer_organization_id));

Line: 206

     SELECT TO_NUMBER(org_information2)
     INTO   l_legal_entity
     FROM   hr_organization_information
     WHERE  org_information_context = 'Accounting Information'
     AND    organization_id = l_org_id;

Line: 216

     UPDATE mtl_material_transactions mmt
     SET    costed_flag = 'E',
            error_code = l_error_code,
            error_explanation = l_error_expl,
            last_update_date = SYSDATE,
            last_updated_by = l_last_updated_by,
            last_update_login = l_last_update_login,
            program_update_date = SYSDATE,
            program_application_id = l_program_application_id,
            program_id = l_program_id,
            request_id = l_request_id
     WHERE  mmt.costed_flag = 'N'
     AND    mmt.organization_id = l_org_id
     AND    DECODE(transaction_action_id,24,transaction_source_type_id,-1)<>14
     AND   (   mmt.acct_period_id IS NULL
            OR mmt.acct_period_id = -1
            OR NOT EXISTS
              (SELECT 1
               FROM   org_acct_periods oap
               WHERE  oap.organization_id = l_org_id
               AND    oap.acct_period_id = mmt.acct_period_id
               AND    mmt.transaction_date - l_timezone_offset BETWEEN
                      oap.period_start_date AND oap.schedule_close_date+.99999));

Line: 244

   /* ignore scrap, wip/inv lot transactions, periodic cost update, pack/unpack, container */
   l_error_code := 'CST_INVALID_CG';

Line: 248

   UPDATE mtl_material_transactions mmt
   SET    costed_flag = 'E',
          error_code = l_error_code,
          error_explanation = l_error_expl,
          last_update_date = SYSDATE,
          last_updated_by = l_last_updated_by,
          last_update_login = l_last_update_login,
          program_update_date = SYSDATE,
          program_application_id = l_program_application_id,
          program_id = l_program_id,
          request_id = l_request_id
   WHERE  mmt.costed_flag = 'N'
   AND    transaction_action_id NOT IN (30,40,41,42,43,50,51,52)
   AND    DECODE(transaction_action_id,24,transaction_source_type_id,-1)<>14
   AND    (
              cost_group_id IS NULL
          OR  (   cost_group_id <> 1
              AND cost_group_id NOT IN (
                    SELECT ccga.cost_group_id
                    FROM   cst_cost_group_accounts ccga
                    WHERE  ccga.organization_id = mmt.organization_id
                  )
              )
          );

Line: 278

   UPDATE mtl_material_transactions mmt
   SET    costed_flag = 'E',
          error_code = l_error_code,
          error_explanation = l_error_expl,
          last_update_date = SYSDATE,
          last_updated_by = l_last_updated_by,
          last_update_login = l_last_update_login,
          program_update_date = SYSDATE,
          program_application_id = l_program_application_id,
          program_id = l_program_id,
          request_id = l_request_id
   WHERE  costed_flag = 'N'
   AND   (   (    transaction_action_id = 12
              AND fob_point = 1)
          OR (    transaction_action_id = 21
              AND fob_point = 2)
          OR  transaction_action_id IN (2,5,28))
   AND    (
              transfer_cost_group_id IS NULL
          OR  (   transfer_cost_group_id <> 1
              AND transfer_cost_group_id NOT IN (
                    SELECT ccga.cost_group_id
                    FROM   cst_cost_group_accounts ccga
                    WHERE  ccga.organization_id = mmt.organization_id
                  )
              )
          );

Line: 311

   UPDATE mtl_material_transactions mmt
   SET    costed_flag = 'E',
          error_code = l_error_code,
          error_explanation = l_error_expl,
          last_update_date = SYSDATE,
          last_updated_by = l_last_updated_by,
          last_update_login = l_last_update_login,
          program_update_date = SYSDATE,
          program_application_id = l_program_application_id,
          program_id = l_program_id,
          request_id = l_request_id
   WHERE  costed_flag = 'N'
   AND   (   (    transaction_action_id = 12
              AND fob_point = 2)
          OR (    transaction_action_id = 21
              AND fob_point = 1)
          OR  transaction_action_id = 3)
   AND    (
              transfer_cost_group_id IS NULL
          OR  (   transfer_cost_group_id <> 1
              AND transfer_cost_group_id NOT IN (
                    SELECT ccga.cost_group_id
                    FROM   cst_cost_group_accounts ccga
                    WHERE  ccga.organization_id = mmt.transfer_organization_id
                  )
              )
          );

Line: 344

   UPDATE mtl_material_transactions mmt
   SET    costed_flag = 'E',
          error_code = l_error_code,
          error_explanation = l_error_expl,
          last_update_date = SYSDATE,
          last_updated_by = l_last_updated_by,
          last_update_login = l_last_update_login,
          program_update_date = SYSDATE,
          program_application_id = l_program_application_id,
          program_id = l_program_id,
          request_id = l_request_id
   WHERE  costed_flag = 'N'
   AND    transaction_action_id in (1,27,29)
   AND    transaction_source_type_id = 6
   AND NOT EXISTS
     (SELECT 1
      FROM   mtl_generic_dispositions mgd
      WHERE  mgd.organization_id = mmt.organization_id
      AND    mgd.disposition_id = mmt.transaction_source_id);

Line: 369

   UPDATE mtl_material_transactions mmt
   SET    costed_flag = 'E',
          error_code = l_error_code,
          error_explanation = l_error_expl,
          last_update_date = SYSDATE,
          last_updated_by = l_last_updated_by,
          last_update_login = l_last_update_login,
          program_update_date = SYSDATE,
          program_application_id = l_program_application_id,
          program_id = l_program_id,
          request_id = l_request_id
   WHERE  mmt.costed_flag = 'N'
   AND    mmt.transaction_action_id in (1,27)
   AND NOT EXISTS
     (SELECT 1
      FROM   mtl_secondary_inventories msi
      WHERE  msi.organization_id = mmt.organization_id
      AND    msi.secondary_inventory_name = mmt.subinventory_code);

Line: 393

   UPDATE mtl_material_transactions mmt
   SET    costed_flag = 'E',
          error_code = l_error_code,
          error_explanation = l_error_expl,
          last_update_date = SYSDATE,
          last_updated_by = l_last_updated_by,
          last_update_login = l_last_update_login,
          program_update_date = SYSDATE,
          program_application_id = l_program_application_id,
          program_id = l_program_id,
          request_id = l_request_id
   WHERE  mmt.costed_flag = 'N'
   AND    mmt.transaction_source_type_id = 5
   AND NOT EXISTS
     (SELECT 1
      FROM   wip_entities we
      WHERE  we.organization_id = mmt.organization_id
      AND    we.wip_entity_id = mmt.transaction_source_id
      AND   (we.entity_type = 4
         OR EXISTS (
             SELECT 1 from wip_period_balances wpb
             WHERE wpb.organization_id = mmt.organization_id
             AND wpb.wip_entity_id = mmt.transaction_source_id
             AND wpb.acct_period_id = mmt.acct_period_id)));

Line: 423

   UPDATE mtl_material_transactions mmt
   SET    costed_flag = 'E',
          error_code = l_error_code,
          error_explanation = l_error_expl,
          last_update_date = SYSDATE,
          last_updated_by = l_last_updated_by,
          last_update_login = l_last_update_login,
          program_update_date = SYSDATE,
          program_application_id = l_program_application_id,
          program_id = l_program_id,
          request_id = l_request_id
   WHERE  costed_flag = 'N'
   AND    transaction_source_type_id = 5
   AND NOT EXISTS
     (SELECT 1
      FROM   wip_discrete_jobs wdj
      WHERE  wdj.organization_id = mmt.organization_id
      AND    wdj.wip_entity_id = mmt.transaction_source_id
      AND    wdj.date_released <= mmt.transaction_date
      UNION ALL
      SELECT 1
      FROM   wip_repetitive_schedules wrs, mtl_material_txn_allocations mmta
      WHERE  wrs.organization_id = mmt.organization_id
      AND    wrs.date_released <= mmt.transaction_date
      AND    mmta.organization_id = mmt.organization_id
      AND    mmta.transaction_id = mmt.transaction_id
      AND    wrs.repetitive_schedule_id = mmta.repetitive_schedule_id
      UNION ALL
      SELECT 1
      FROM   wip_entities we
      WHERE  we.organization_id = mmt.organization_id
      AND    we.wip_entity_id = mmt.transaction_source_id
      AND    we.entity_type = 4
     );