DBA Data[Home] [Help]

APPS.CST_INVENTORY_PVT SQL Statements

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

Line: 86

    SELECT cost_organization_id,
           primary_cost_method
    INTO   l_cost_org_id,
           l_primary_cost_method
    FROM   mtl_parameters
    WHERE  organization_id = p_organization_id;
Line: 95

    SELECT default_cost_type_id
    INTO   l_def_cost_type_id
    FROM   cst_cost_types
    WHERE  cost_type_id = NVL(p_cost_type_id, l_primary_cost_method);
Line: 123

      INSERT INTO   cst_item_list_temp(
             inventory_item_id,
             category_id,
             cost_type_id
      )
        SELECT MSI.inventory_item_id,
                MIC.category_id,
                CIC.cost_type_id
         FROM   mtl_item_categories MIC,
                mtl_system_items_kfv MSI,
                cst_item_costs CIC
--              cst_item_costs CIC1,
--              cst_item_costs CIC2
         WHERE  MIC.category_set_id = p_category_set_id
         AND    MIC.organization_id = p_organization_id
         AND    MSI.organization_id = p_organization_id
         AND    MSI.inventory_item_id = MIC.inventory_item_id
--{
--         AND    MSI.concatenated_segments
--                  BETWEEN NVL(p_item_from,MSI.concatenated_segments)
--                  AND     NVL(p_item_to,MSI.concatenated_segments)
--         AND    CIC1.organization_id (+) = l_cost_org_id
--         AND    CIC2.organization_id (+) = l_cost_org_id
--         AND    CIC1.inventory_item_id (+) = MSI.inventory_item_id
--         AND    CIC2.inventory_item_id (+) = MSI.inventory_item_id
--         AND    CIC1.cost_type_id (+) = NVL(p_cost_type_id,l_primary_cost_method)
--         AND    CIC2.cost_type_id (+) = l_def_cost_type_id
--         AND    CIC.rowid = NVL(CIC1.rowid,CIC2.rowid)
         AND CIC.organization_id    = p_organization_id
         AND CIC.inventory_item_id  = MSI.inventory_item_id
         AND CIC.cost_type_id       = l_primary_cost_method
--}
         AND CIC.inventory_asset_flag =
             DECODE(p_expense_item,1,CIC.inventory_asset_flag,1)
         AND NVL(CIC.item_cost,0) = DECODE(p_zero_cost_only,
                                         1,0,
                                         NVL(CIC.item_cost,0)
                                  );
Line: 165

    INSERT INTO   cst_item_list_temp(
             inventory_item_id,
             category_id,
             cost_type_id
           )
    SELECT MSI.inventory_item_id,
           MIC.category_id,
           CIC.cost_type_id
    FROM   mtl_item_categories MIC,
           mtl_system_items_kfv MSI,
           cst_item_costs CIC,
           cst_item_costs CIC1,
           cst_item_costs CIC2
    WHERE  MIC.category_set_id = p_category_set_id
    AND    MIC.organization_id = p_organization_id
    AND    MSI.organization_id = p_organization_id
    AND    MSI.inventory_item_id = MIC.inventory_item_id
    AND    MSI.concatenated_segments
             BETWEEN NVL(p_item_from,MSI.concatenated_segments)
             AND     NVL(p_item_to,MSI.concatenated_segments)
    AND    CIC1.organization_id (+) = l_cost_org_id
    AND    CIC2.organization_id (+) = l_cost_org_id
    AND    CIC1.inventory_item_id (+) = MSI.inventory_item_id
    AND    CIC2.inventory_item_id (+) = MSI.inventory_item_id
    AND    CIC1.cost_type_id (+) = NVL(p_cost_type_id,l_primary_cost_method)
    AND    CIC2.cost_type_id (+) = l_def_cost_type_id
    AND    CIC.rowid = NVL(CIC1.rowid,CIC2.rowid)
    AND    CIC.inventory_asset_flag =
           DECODE(p_expense_item,1,CIC.inventory_asset_flag,1)
    AND    NVL(CIC.item_cost,0) = DECODE(p_zero_cost_only,
                                         1,0,
                                         NVL(CIC.item_cost,0)
                                  );
Line: 213

      INSERT
      INTO   cst_item_list_temp(
               inventory_item_id,
               category_id,
               cost_type_id
             )
      SELECT MSI.inventory_item_id,
             MIC.category_id,
             CIC.cost_type_id
      FROM   mtl_item_categories MIC,
             mtl_categories_kfv MC,
             mtl_system_items_kfv MSI,
             cst_item_costs CIC
--             cst_item_costs CIC1,
--             cst_item_costs CIC2
      WHERE  MC.concatenated_segments
             BETWEEN NVL(p_category_from,MC.concatenated_segments)
             AND     NVL(p_category_to,MC.concatenated_segments)
      AND    MC.structure_id = (SELECT structure_id FROM mtl_category_sets WHERE category_set_id = p_category_set_id)
      AND    MIC.category_id = MC.category_id
      AND    MIC.category_set_id = p_category_set_id
      AND    MIC.organization_id = p_organization_id
      AND    MSI.organization_id = p_organization_id
      AND    MSI.inventory_item_id = MIC.inventory_item_id
      AND    MSI.concatenated_segments
             BETWEEN NVL(p_item_from,MSI.concatenated_segments)
             AND     NVL(p_item_to,MSI.concatenated_segments)
             -- The join to CIC implies that the item is
             -- MSI.costing_enabled
--{
--      AND    CIC1.organization_id (+) = l_cost_org_id
--      AND    CIC2.organization_id (+) = l_cost_org_id
--      AND    CIC1.inventory_item_id (+) = MSI.inventory_item_id
--      AND    CIC2.inventory_item_id (+) = MSI.inventory_item_id
--      AND    CIC1.cost_type_id (+) = NVL(p_cost_type_id,l_primary_cost_method)
--      AND    CIC2.cost_type_id (+) = l_def_cost_type_id
--      AND    CIC.rowid = NVL(CIC1.rowid,CIC2.rowid)
         AND CIC.organization_id    = p_organization_id
         AND CIC.inventory_item_id  = MSI.inventory_item_id
         AND CIC.cost_type_id       = l_primary_cost_method
--}
      AND    CIC.inventory_asset_flag =
             DECODE(p_expense_item,1,CIC.inventory_asset_flag,1)
      AND    NVL(CIC.item_cost,0) = DECODE(p_zero_cost_only,
                                      1,0,
                                      NVL(CIC.item_cost,0)
                                    );
Line: 264

      INSERT
      INTO   cst_item_list_temp(
               inventory_item_id,
               category_id,
               cost_type_id
             )
      SELECT MSI.inventory_item_id,
             MIC.category_id,
             CIC.cost_type_id
      FROM   mtl_item_categories MIC,
             mtl_categories_kfv MC,
             mtl_system_items_kfv MSI,
             cst_item_costs CIC,
             cst_item_costs CIC1,
             cst_item_costs CIC2
      WHERE  MC.concatenated_segments
             BETWEEN NVL(p_category_from,MC.concatenated_segments)
             AND     NVL(p_category_to,MC.concatenated_segments)
      AND    MC.structure_id = (SELECT structure_id FROM mtl_category_sets WHERE category_set_id = p_category_set_id)
      AND    MIC.category_id = MC.category_id
      AND    MIC.category_set_id = p_category_set_id
      AND    MIC.organization_id = p_organization_id
      AND    MSI.organization_id = p_organization_id
      AND    MSI.inventory_item_id = MIC.inventory_item_id
      AND    MSI.concatenated_segments
             BETWEEN NVL(p_item_from,MSI.concatenated_segments)
             AND     NVL(p_item_to,MSI.concatenated_segments)
             -- The join to CIC implies that the item is
             -- MSI.costing_enabled
      AND    CIC1.organization_id (+) = l_cost_org_id
      AND    CIC2.organization_id (+) = l_cost_org_id
      AND    CIC1.inventory_item_id (+) = MSI.inventory_item_id
      AND    CIC2.inventory_item_id (+) = MSI.inventory_item_id
      AND    CIC1.cost_type_id (+) = NVL(p_cost_type_id,l_primary_cost_method)
      AND    CIC2.cost_type_id (+) = l_def_cost_type_id
      AND    CIC.rowid = NVL(CIC1.rowid,CIC2.rowid)
      AND    CIC.inventory_asset_flag =
             DECODE(p_expense_item,1,CIC.inventory_asset_flag,1)
      AND    NVL(CIC.item_cost,0) = DECODE(p_zero_cost_only,
                                      1,0,
                                      NVL(CIC.item_cost,0)
                                    );
Line: 316

          p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
                          ' items into CILT'||
                          ' - include costing_enabled items only'
        );
Line: 327

      INSERT
      INTO   cst_item_list_temp(
               inventory_item_id,
               category_id,
               cost_type_id
             )
      SELECT MSI.inventory_item_id,
             MIC.category_id,
             l_def_cost_type_id
      FROM   mtl_item_categories MIC,
             mtl_categories_kfv MC,
             mtl_system_items_kfv MSI
      WHERE  MC.concatenated_segments
             BETWEEN NVL(p_category_from,MC.concatenated_segments)
             AND     NVL(p_category_to,MC.concatenated_segments)
      AND    MC.structure_id = (SELECT structure_id FROM mtl_category_sets WHERE category_set_id = p_category_set_id)
      AND    MIC.category_id = MC.category_id
      AND    MIC.category_set_id = p_category_set_id
      AND    MIC.organization_id = p_organization_id
      AND    MSI.organization_id = p_organization_id
      AND    MSI.inventory_item_id = MIC.inventory_item_id
      AND    MSI.concatenated_segments
             BETWEEN NVL(p_item_from,MSI.concatenated_segments)
             AND     NVL(p_item_to,MSI.concatenated_segments);
Line: 357

          p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
                          ' items into CILT'||
                          ' - include non-costing-enabled items'
        );
Line: 364

      INSERT
      INTO   cst_item_list_temp(
               inventory_item_id,
               category_id,
               cost_type_id
             )
      VALUES
      (
               NULL,
               NULL,
               l_def_cost_type_id
      );
Line: 382

          p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
                         ' items into CILT'||
                         ' - for one-time items'
        );
Line: 460

    INSERT
    INTO   cst_cg_list_temp(
             cost_group_id
           )
    SELECT CCG.cost_group_id
    FROM   cst_cost_groups CCG,
           (SELECT organization_id,
                   cost_group_id
            FROM   cst_cost_group_accounts
            UNION
            /* This is required for cases where default
               cost group id is 1 and it doesn't exist
               in cst_cost_group_accounts */
            SELECT organization_id,
                   default_cost_group_id cost_group_id
            FROM   mtl_parameters
           ) CCGA
    WHERE  CCGA.organization_id = p_organization_id
    AND    CCG.cost_group_id = CCGA.cost_group_id
    AND    NVL(CCG.disable_date, sysdate+1) > sysdate
    AND    CCG.cost_group
           BETWEEN NVL(p_cost_group_from, CCG.cost_group)
           AND     NVL(p_cost_group_to, CCG.cost_group)
    AND NOT EXISTS( SELECT 'Cost Group already exists'
                       FROM   cst_cg_list_temp CGLT
                       where CGLT.cost_group_id = CCG.cost_group_id
                  );
Line: 494

        p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
                        ' cost groups from organization '||p_organization_id||
                        ' into CCLT'
      );
Line: 504

      INSERT
      INTO   cst_cg_list_temp(
               cost_group_id
             )
     (SELECT DISTINCT
             CCG.cost_group_id
      FROM   cst_cost_groups CCG,
             (SELECT organization_id,
                     cost_group_id
              FROM   cst_cost_group_accounts
              UNION
              /* This is required for cases where default
                 cost group id is 1 and it doesn't exist
                 in cst_cost_group_accounts */
              SELECT organization_id,
                     default_cost_group_id cost_group_id
              FROM   mtl_parameters
             ) CCGA,
             mtl_interorg_parameters MIP
      WHERE  CCG.cost_group_id = CCGA.cost_group_id
      AND    NVL(CCG.disable_date, sysdate+1) > sysdate
      AND    CCG.cost_group
             BETWEEN NVL(p_cost_group_from, CCG.cost_group)
             AND     NVL(p_cost_group_to, CCG.cost_group)
      AND    (  (    MIP.from_organization_id = p_organization_id
                 AND MIP.to_organization_id = CCGA.organization_id)
              OR
                (    MIP.to_organization_id = p_organization_id
                 AND MIP.from_organization_id = CCGA.organization_id)
             )
      MINUS
      SELECT cost_group_id
      FROM   cst_cg_list_temp);
Line: 543

          p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
                           ' cost groups from other organizations '||
                           ' into CCLT'
        );
Line: 618

    INSERT
    INTO   cst_sub_list_temp(
             subinventory_code
           )
    SELECT SUB.secondary_inventory_name
    FROM   mtl_secondary_inventories SUB
    WHERE  SUB.organization_id = p_organization_id
    AND    SUB.asset_inventory = DECODE(p_expense_sub,1,SUB.asset_inventory,1)
           -- Non-quantity tracked subinventories do not appear in MOQ.
    AND    SUB.quantity_tracked = 1
    AND    SUB.secondary_inventory_name
           BETWEEN NVL(p_subinventory_from, SUB.secondary_inventory_name)
           AND     NVL(p_subinventory_to, SUB.secondary_inventory_name)
    AND NOT EXISTS ( SELECT 'Subinventory Already Exists'
                         FROM cst_sub_list_temp CSLT
                         where CSLT.subinventory_code = SUB.secondary_inventory_name
                    );
Line: 642

        p_error_text => l_stmt_num||': Inserted '||SQL%ROWCOUNT||
                        ' subinventories into CSLT'
      );
Line: 717

    INSERT
    INTO   cst_inv_qty_temp(
             organization_id,
             cost_group_id,
             subinventory_code,
             inventory_item_id,
             rollback_qty,
             qty_source,
             revision,
             category_id,
             cost_type_id
           )
    SELECT p_organization_id,
           MOQ.cost_group_id,
           MOQ.subinventory_code,
           MOQ.inventory_item_id,
           SUM(MOQ.transaction_quantity),
           3, -- CURRENT_ONHAND
           DECODE(p_qty_by_revision,1,moq.revision,NULL),
           CILT.category_id,
           CILT.cost_type_id
    FROM   mtl_onhand_quantities MOQ,
           cst_item_list_temp CILT,
           cst_cg_list_temp CCLT,
           cst_sub_list_temp CSLT
    WHERE  MOQ.organization_id  = p_organization_id
    AND    CILT.inventory_item_id = MOQ.inventory_item_id
    AND    CCLT.cost_group_id = MOQ.cost_group_id
    AND    CSLT.subinventory_code = MOQ.subinventory_code
    GROUP
    BY     MOQ.cost_group_id,
           MOQ.subinventory_code,
           MOQ.inventory_item_id,
           DECODE(p_qty_by_revision,1,moq.revision,NULL),
           CILT.category_id,
           CILT.cost_type_id;
Line: 768

      INSERT
      INTO   cst_inv_qty_temp(
               organization_id,
               cost_group_id,
               subinventory_code,
               inventory_item_id,
               rollback_qty,
               qty_source,
               revision,
               txn_source_type_id,
               category_id,
               cost_type_id
              )
      SELECT /*+ LEADING (MMT)*/
             p_organization_id,
             MMT.cost_group_id,
             MMT.subinventory_code,
             MMT.inventory_item_id,
             -1*SUM(MMT.primary_quantity),
             -- Sum is used to reduce the number of rows in CIQT
             4, -- UNCOSTED_ONHAND
             DECODE(p_qty_by_revision, 1, MMT.revision, NULL),
             MMT.transaction_source_type_id,
             CILT.category_id,
             CILT.cost_type_id
      FROM   mtl_material_transactions MMT,
             cst_item_list_temp CILT,
             cst_cg_list_temp CCLT,
             cst_sub_list_temp CSLT
      WHERE  MMT.organization_id  = p_organization_id
      AND    CILT.inventory_item_id = MMT.inventory_item_id
      AND    CCLT.cost_group_id = MMT.cost_group_id
      AND    CSLT.subinventory_code = MMT.subinventory_code
      AND    MMT.costed_flag in ('N','E')
             -- Ignore consigned transactions
      AND    MMT.organization_id =
             NVL(MMT.owning_organization_id, MMT.organization_id)
      AND    NVL(MMT.owning_tp_type,2) = 2
             -- Ignore logical transactions corresponding to drop shipments
             -- and global procurement transactions
      AND    NVL(MMT.logical_transaction,-1) <> 1
             -- Ignore WMS/OSFM transactions, cost updates including periodic cost
             -- updates that do not affect onhand quantity
      AND    MMT.transaction_action_id NOT IN (24,40,41,50,51,52)
      GROUP
      BY     MMT.cost_group_id,
             MMT.subinventory_code,
             MMT.inventory_item_id,
             DECODE(p_qty_by_revision, 1, MMT.revision, NULL),
             MMT.transaction_source_type_id,
             CILT.category_id,
             CILT.cost_type_id;
Line: 838

      INSERT
      INTO   cst_inv_qty_temp
             ( organization_id,
               cost_group_id,
               subinventory_code,
               inventory_item_id,
               rollback_qty,
               qty_source,
               rollback_value,
               revision,
               txn_source_type_id,
               category_id,
               cost_type_id
             )
      SELECT p_organization_id,
             MMT.cost_group_id,
             MMT.subinventory_code,
             MMT.inventory_item_id,
             -- There is a bug on Average Cost Update, where primary_quantity
             -- is populated in addition to quantity_adjusted
             SUM(-1*DECODE(MMT.transaction_action_id,24,0,MMT.primary_quantity)),
             -- Sum is used to reduce the number of rows in CIQT
             5, -- ROLLBACK_ONHAND
             SUM(
               DECODE(
                 MMT.transaction_action_id,
                 24, MMT.quantity_adjusted*(MMT.new_cost - MMT.prior_cost),
                 MMT.primary_quantity*MMT.actual_cost - NVL(MMT.variance_amount,0)
               )
             ),
             -- Rollback value is used in the Transaction Value Historical
             -- Summary - Average Costing report
             DECODE(p_qty_by_revision, 1, MMT.revision, NULL),
             MMT.transaction_source_type_id,
             CILT.category_id,
             CILT.cost_type_id
      FROM   mtl_material_transactions MMT,
             cst_item_list_temp CILT
      WHERE  MMT.organization_id = p_organization_id
      AND    CILT.inventory_item_id = MMT.inventory_item_id
      AND    MMT.costed_flag IS NULL
      AND    MMT.transaction_date > p_valuation_date
             -- Ignore Consigned transactions
      AND    MMT.organization_id = NVL(MMT.owning_organization_id,
             MMT.organization_id)
      AND    NVL(MMT.owning_tp_type,2) = 2
             -- Ignore logical transactions corresponding to drop shipments
             -- and global procurement transactions
      AND    NVL(MMT.logical_transaction,-1) <> 1
             -- Ignore WMS and OSFM transactions that do not affect onhand
             -- quantity and inventory valuation
      AND    MMT.transaction_action_id NOT IN (40,41,50,51,52)
             -- Ignore periodic cost updates
      AND    MMT.transaction_source_type_id <> 14
             -- The only transactions other than the ones ignored above that
             -- affect inventory valuation and have null cost_group_id are
             -- standard cost updates (non-PJM/WMS)
      AND    (   (    MMT.transaction_type_id = 24
                  AND MMT.cost_group_id IS NULL
                 )
              OR EXISTS (
                   SELECT 1
                   FROM   cst_cg_list_temp CCLT
                   WHERE  CCLT.cost_group_id = MMT.cost_group_id)
             )
             -- The only transactions other than the ones ignored above that
             -- affect inventory valuation and have null subinventory_code are
             -- actual cost updates and std cost updates for PJM/WMS orgs
      AND    (   (    MMT.transaction_action_id = 24
                  AND MMT.subinventory_code IS NULL
                 )
              OR EXISTS (
                   SELECT 1
                   FROM   cst_sub_list_temp CSLT
                   WHERE  CSLT.subinventory_code = MMT.subinventory_code)
             )
      GROUP
      BY     MMT.cost_group_id,
             MMT.subinventory_code,
             MMT.inventory_item_id,
             DECODE(p_qty_by_revision, 1, MMT.revision, NULL),
             MMT.transaction_source_type_id,
             CILT.category_id,
             CILT.cost_type_id;
Line: 938

      INSERT
      INTO   cst_inv_qty_temp(
               organization_id,
               inventory_item_id,
               rollback_qty,
               qty_source,
               category_id,
               cost_group_id,
               cost_type_id
             )
      SELECT p_organization_id,
             TEMP.inventory_item_id,
             0,
             3, -- CURRENT_ONHAND
             TEMP.category_id,
             MP.default_cost_group_id,
             TEMP.cost_type_id
      FROM   (
               SELECT inventory_item_id,
                      category_id,
                      cost_type_id
               FROM   cst_item_list_temp
               MINUS
               SELECT DISTINCT
                      inventory_item_id,
                      category_id,
                      cost_type_id
               FROM   cst_inv_qty_temp
               WHERE  organization_id = p_organization_id
             ) TEMP,
             mtl_parameters MP
      WHERE  MP.organization_id = p_organization_id;
Line: 1063

         SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */ 1
           FROM mtl_material_transactions MMT
          WHERE mmt.costed_flag IN ('N','E')
            AND (    mmt.organization_id = p_organization_id
			      OR mmt.transfer_organization_id = p_organization_id)
            AND (    mmt.transaction_action_id = 12
                  OR mmt.transaction_action_id = 21)
            AND ROWNUM <2;
Line: 1107

        INSERT
        INTO   cst_inv_qty_temp(
                 qty_source,
                 organization_id,
                 inventory_item_id,
                 category_id,
                 revision,
                 cost_type_id,
                 cost_group_id,
                 from_organization_id,
                 to_organization_id,
                 rollback_qty,
                 intransit_inv_account,
                 shipment_line_id
               )
        SELECT 6,-- CURRENT_INTRANSIT
               MS.intransit_owning_org_id,
               ITEMS.inventory_item_id,
               ITEMS.category_id,
               MS.item_revision,
               ITEMS.cost_type_id,
               CGS.cost_group_id,
               MS.from_organization_id,
               MS.to_organization_id,
               -- quantity is always expressed in the primary unit of measure
               -- of the intransit owning organization
               SUM(
                 DECODE(
                   MS.intransit_owning_org_id,
                   MS.from_organization_id,
                   inv_convert.inv_um_convert(
                     MS.item_id,NULL,MS.quantity,NULL,NULL,
                     MS.unit_of_measure,MSI_FROM.primary_unit_of_measure
                   ),
                   MS.to_org_primary_quantity
                 )
               ),
               NVL(
                 MMT.intransit_account,
                 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
               ),
               MS.shipment_line_id
        FROM   mtl_supply MS,
               cst_item_list_temp ITEMS,
               cst_cg_list_temp CGS,
               mtl_parameters MP,
               mtl_interorg_parameters MIP,
               mtl_material_transactions MMT,
               rcv_shipment_lines RSL,
               mtl_system_items MSI_FROM
        WHERE  MS.to_organization_id = p_organization_id
      /*  AND    MS.intransit_owning_org_id = p_organization_id */ /* Bug 5664736 */
      	AND    MS.intransit_owning_org_id = DECODE(NVL(p_own,-1),1,p_organization_id,MS.intransit_owning_org_id)
        AND    MS.item_id = ITEMS.inventory_item_id
        AND    MS.supply_type_code IN ('SHIPMENT','RECEIVING')
        AND    MS.destination_type_code = 'INVENTORY'
        AND    NVL(MS.cost_group_id,MP.default_cost_group_id) = CGS.cost_group_id
        AND    MP.organization_id = MS.intransit_owning_org_id
        AND    RSL.shipment_line_id = MS.shipment_line_id
        AND    MMT.transaction_id (+) = RSL.mmt_transaction_id
        AND    MIP.from_organization_id (+) = MS.from_organization_id
        AND    MIP.to_organization_id (+) = MS.to_organization_id
        AND    MIP.fob_point (+) =
               DECODE(
                 MS.intransit_owning_org_id,
                 MS.from_organization_id, 2,
                 MS.to_organization_id, 1
               )
        AND    MSI_FROM.inventory_item_id = MS.item_id
        AND    MSI_FROM.organization_id = MS.from_organization_id
        GROUP
        BY     MS.intransit_owning_org_id,
               ITEMS.inventory_item_id,
               ITEMS.category_id,
               MS.item_revision,
               ITEMS.cost_type_id,
               CGS.cost_group_id,
               MS.from_organization_id,
               MS.to_organization_id,
               NVL(
                 MMT.intransit_account,
                 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
               ),
               MS.shipment_line_id;
Line: 1210

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account,
                   shipment_line_id
                 )
          SELECT 7, -- UNCOSTED_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
                     ),
                     2,
                     MMT.primary_quantity
                   )
                 ),
                 NVL(MMT.intransit_account,MIP.intransit_inv_account),
                 RSL.shipment_line_id
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_TO,
                 rcv_shipment_headers RSH,
                 rcv_shipment_lines RSL
          WHERE  MMT.transfer_organization_id = p_organization_id
          AND    MMT.transaction_action_id = 21
          AND    MMT.costed_flag IN ('N','E')
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_cost_group_id,
                   2,MMT.cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.transfer_organization_id
          AND    MIP.from_organization_id = MMT.organization_id
          AND    MSI_TO.organization_id = MMT.transfer_organization_id
          AND    MSI_TO.inventory_item_id = MMT.inventory_item_id
          AND    RSH.shipment_num = MMT.shipment_number
          AND    RSL.shipment_header_id = RSH.shipment_header_id
          AND    RSL.mmt_transaction_id = MMT.transaction_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account,MIP.intransit_inv_account),
                 RSL.shipment_line_id;
Line: 1306

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account,
                   shipment_line_id
                 )
          SELECT 7, -- UNCOSTED_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.transfer_organization_id,
                 MMT.organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     MMT.primary_quantity,
                     2,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
                     )
                   )
                 ),
                 NVL(MMT.intransit_account, MIP.intransit_inv_account),
                 RT.shipment_line_id
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_FROM,
                 rcv_transactions RT
          WHERE  MMT.organization_id = p_organization_id
          AND    MMT.transaction_action_id = 12
          AND    MMT.costed_flag IN ('N', 'E')
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.cost_group_id,
                   2,MMT.transfer_cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.organization_id
          AND    MIP.from_organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.inventory_item_id = MMT.inventory_item_id
          AND    RT.transaction_id = MMT.rcv_transaction_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account, MIP.intransit_inv_account),
                 RT.shipment_line_id;
Line: 1407

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account,
                   shipment_line_id
                 )
          SELECT 8, -- ROLLBACK_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
                     ),
                     2,
                     MMT.primary_quantity
                   )
                 ),
                 NVL(MMT.intransit_account,MIP.intransit_inv_account),
                 RSL.shipment_line_id
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_TO,
                 rcv_shipment_headers RSH,
                 rcv_shipment_lines RSL
          WHERE  MMT.transfer_organization_id = p_organization_id
          AND    MMT.transaction_action_id = 21
          AND    MMT.costed_flag IS NULL
          AND    MMT.transaction_date > p_valuation_date
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_cost_group_id,
                   2,MMT.cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.transfer_organization_id
          AND    MIP.from_organization_id = MMT.organization_id
          AND    MSI_TO.organization_id = MMT.transfer_organization_id
          AND    MSI_TO.inventory_item_id = MMT.inventory_item_id
          AND    RSH.shipment_num = MMT.shipment_number
          AND    RSL.shipment_header_id = RSH.shipment_header_id
          AND    RSL.mmt_transaction_id = MMT.transaction_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account,MIP.intransit_inv_account),
                 RSL.shipment_line_id;
Line: 1508

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account,
                   shipment_line_id
                 )
          SELECT 8, -- ROLLBACK_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.transfer_organization_id,
                 MMT.organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     MMT.primary_quantity,
                     2,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
                     )
                   )
                 ),
                 NVL(MMT.intransit_account, MIP.intransit_inv_account),
                 RT.shipment_line_id
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_FROM,
                 rcv_transactions RT
          WHERE  MMT.organization_id = p_organization_id
          AND    MMT.transaction_action_id = 12
          AND    MMT.costed_flag IS NULL
          AND    MMT.transaction_date > p_valuation_date
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.cost_group_id,
                   2,MMT.transfer_cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.organization_id
          AND    MIP.from_organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.inventory_item_id = MMT.inventory_item_id
          AND    RT.transaction_id = MMT.rcv_transaction_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account, MIP.intransit_inv_account),
                 RT.shipment_line_id;
Line: 1609

        INSERT
        INTO   cst_inv_qty_temp(
                 qty_source,
                 organization_id,
                 inventory_item_id,
                 category_id,
                 revision,
                 cost_type_id,
                 cost_group_id,
                 from_organization_id,
                 to_organization_id,
                 rollback_qty,
                 intransit_inv_account,
                 shipment_line_id
               )
        SELECT 6,-- CURRENT_INTRANSIT
               MS.intransit_owning_org_id,
               ITEMS.inventory_item_id,
               ITEMS.category_id,
               MS.item_revision,
               ITEMS.cost_type_id,
               CGS.cost_group_id,
               MS.from_organization_id,
               MS.to_organization_id,
               SUM(
                 DECODE(
                   MS.intransit_owning_org_id,
                   MS.from_organization_id,
                   inv_convert.inv_um_convert(
                     MS.item_id,NULL,MS.quantity,NULL,NULL,
                     MS.unit_of_measure,MSI_FROM.primary_unit_of_measure
                   ),
                   MS.to_org_primary_quantity
                 )
               ),
               NVL(
                 MMT.intransit_account,
                 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
               ),
               MS.shipment_line_id
        FROM   mtl_supply MS,
               cst_item_list_temp ITEMS,
               cst_cg_list_temp CGS,
               mtl_parameters MP,
               mtl_interorg_parameters MIP,
               mtl_material_transactions MMT,
               rcv_shipment_lines RSL,
               mtl_system_items MSI_FROM
        WHERE  MS.from_organization_id = p_organization_id
      /*  AND    MS.intransit_owning_org_id = p_organization_id */ /* Bug 5664736 */
      	AND    MS.intransit_owning_org_id = DECODE(NVL(p_own,-1),1,p_organization_id,MS.intransit_owning_org_id)
        AND    MS.item_id = ITEMS.inventory_item_id
        AND    MS.supply_type_code IN ('SHIPMENT','RECEIVING')
        AND    MS.destination_type_code = 'INVENTORY'
        AND    NVL(MS.cost_group_id,MP.default_cost_group_id) = CGS.cost_group_id
        AND    MP.organization_id = MS.intransit_owning_org_id
        AND    RSL.shipment_line_id = MS.shipment_line_id
        AND    MMT.transaction_id (+) = RSL.mmt_transaction_id
        AND    MIP.from_organization_id (+) = MS.from_organization_id
        AND    MIP.to_organization_id (+) = MS.to_organization_id
        AND    MIP.fob_point (+) =
               DECODE(
                 MS.intransit_owning_org_id,
                 MS.from_organization_id, 2,
                 MS.to_organization_id, 1)
        AND    MSI_FROM.inventory_item_id = MS.item_id
        AND    MSI_FROM.organization_id = MS.from_organization_id
        GROUP
        BY     MS.intransit_owning_org_id,
               ITEMS.inventory_item_id,
               ITEMS.category_id,
               MS.item_revision,
               ITEMS.cost_type_id,
               CGS.cost_group_id,
               MS.from_organization_id,
               MS.to_organization_id,
               NVL(
                 MMT.intransit_account,
                 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
               ),
               MS.shipment_line_id;
Line: 1713

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account,
                   shipment_line_id
                 )
          SELECT 7, -- UNCOSTED_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
                     ),
                     2,
                     MMT.primary_quantity
                   )
                 ),
                 NVL(MMT.intransit_account,MIP.intransit_inv_account),
                 RSL.shipment_line_id
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_TO,
                 rcv_shipment_headers RSH,
                 rcv_shipment_lines RSL
          WHERE  MMT.organization_id = p_organization_id
          AND    MMT.transaction_action_id = 21
          AND    MMT.costed_flag IN ('N','E')
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_cost_group_id,
                   2,MMT.cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.transfer_organization_id
          AND    MIP.from_organization_id = MMT.organization_id
          AND    MSI_TO.organization_id = MMT.transfer_organization_id
          AND    MSI_TO.inventory_item_id = MMT.inventory_item_id
          AND    RSH.shipment_num = MMT.shipment_number
          AND    RSL.shipment_header_id = RSH.shipment_header_id
          AND    RSL.mmt_transaction_id = MMT.transaction_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account,MIP.intransit_inv_account),
                 RSL.shipment_line_id;
Line: 1813

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account,
                   shipment_line_id
                 )
          SELECT 7, -- UNCOSTED_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.transfer_organization_id,
                 MMT.organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     MMT.primary_quantity,
                     2,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
                     )
                   )
                 ),
                 NVL(MMT.intransit_account, MIP.intransit_inv_account),
                 RT.shipment_line_id
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_FROM,
                 rcv_transactions RT
          WHERE  MMT.transfer_organization_id = p_organization_id
          AND    MMT.transaction_action_id = 12
          AND    MMT.costed_flag IN ('N', 'E')
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.cost_group_id,
                   2,MMT.transfer_cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.organization_id
          AND    MIP.from_organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.inventory_item_id = MMT.inventory_item_id
          AND    RT.transaction_id = MMT.rcv_transaction_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account, MIP.intransit_inv_account),
                 RT.shipment_line_id;
Line: 1914

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account,
                   shipment_line_id
                 )
          SELECT 8, -- ROLLBACK_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
                     ),
                     2,
                     MMT.primary_quantity
                   )
                 ),
                 NVL(MMT.intransit_account,MIP.intransit_inv_account),
                 RSL.shipment_line_id
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_TO,
                 rcv_shipment_headers RSH,
                 rcv_shipment_lines RSL
          WHERE  MMT.organization_id = p_organization_id
          AND    MMT.transaction_action_id = 21
          AND    MMT.costed_flag IS NULL
          AND    MMT.transaction_date > p_valuation_date
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_cost_group_id,
                   2,MMT.cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.transfer_organization_id
          AND    MIP.from_organization_id = MMT.organization_id
          AND    MSI_TO.organization_id = MMT.transfer_organization_id
          AND    MSI_TO.inventory_item_id = MMT.inventory_item_id
          AND    RSH.shipment_num = MMT.shipment_number
          AND    RSL.shipment_header_id = RSH.shipment_header_id
          AND    RSL.mmt_transaction_id = MMT.transaction_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account,MIP.intransit_inv_account),
                 RSL.shipment_line_id;
Line: 2015

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account,
                   shipment_line_id
                 )
          SELECT 8, -- ROLLBACK_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.transfer_organization_id,
                 MMT.organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     MMT.primary_quantity,
                     2,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
                     )
                   )
                 ),
                 NVL(MMT.intransit_account, MIP.intransit_inv_account),
                 RT.shipment_line_id
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_FROM,
                 rcv_transactions RT
          WHERE  MMT.transfer_organization_id = p_organization_id
          AND    MMT.transaction_action_id = 12
          AND    MMT.costed_flag IS NULL
          AND    MMT.transaction_date > p_valuation_date
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.cost_group_id,
                   2,MMT.transfer_cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.organization_id
          AND    MIP.from_organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.inventory_item_id = MMT.inventory_item_id
          AND    RT.transaction_id = MMT.rcv_transaction_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account, MIP.intransit_inv_account),
                 RT.shipment_line_id;
Line: 2121

        INSERT
        INTO   cst_inv_qty_temp(
                 qty_source,
                 organization_id,
                 inventory_item_id,
                 category_id,
                 revision,
                 cost_type_id,
                 cost_group_id,
                 from_organization_id,
                 to_organization_id,
                 rollback_qty,
                 intransit_inv_account
               )
        SELECT 6,-- CURRENT_INTRANSIT
               MS.intransit_owning_org_id,
               ITEMS.inventory_item_id,
               ITEMS.category_id,
               MS.item_revision,
               ITEMS.cost_type_id,
               CGS.cost_group_id,
               MS.from_organization_id,
               MS.to_organization_id,
               -- quantity is always expressed in the primary unit of measure
               -- of the intransit owning organization
               SUM(
                 DECODE(
                   MS.intransit_owning_org_id,
                   MS.from_organization_id,
                   inv_convert.inv_um_convert(
                     MS.item_id,NULL,MS.quantity,NULL,NULL,
                     MS.unit_of_measure,MSI_FROM.primary_unit_of_measure
                   ),
                   MS.to_org_primary_quantity
                 )
               ),
               NVL(
                 MMT.intransit_account,
                 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
               )
        FROM   mtl_supply MS,
               cst_item_list_temp ITEMS,
               cst_cg_list_temp CGS,
               mtl_parameters MP,
               mtl_interorg_parameters MIP,
               mtl_material_transactions MMT,
               rcv_shipment_lines RSL,
               mtl_system_items MSI_FROM
        WHERE  MS.to_organization_id = p_organization_id
      /*  AND    MS.intransit_owning_org_id = p_organization_id */ /* Bug 5664736 */
      	AND    MS.intransit_owning_org_id = DECODE(NVL(p_own,-1),1,p_organization_id,MS.intransit_owning_org_id)
        AND    MS.item_id = ITEMS.inventory_item_id
        AND    MS.supply_type_code IN ('SHIPMENT','RECEIVING')
        AND    MS.destination_type_code = 'INVENTORY'
        AND    NVL(MS.cost_group_id,MP.default_cost_group_id) = CGS.cost_group_id
        AND    MP.organization_id = MS.intransit_owning_org_id
        AND    RSL.shipment_line_id = MS.shipment_line_id
        AND    MMT.transaction_id (+) = RSL.mmt_transaction_id
        AND    MIP.from_organization_id (+) = MS.from_organization_id
        AND    MIP.to_organization_id (+) = MS.to_organization_id
        AND    MIP.fob_point (+) =
               DECODE(
                 MS.intransit_owning_org_id,
                 MS.from_organization_id, 2,
                 MS.to_organization_id, 1
               )
        AND    MSI_FROM.inventory_item_id = MS.item_id
        AND    MSI_FROM.organization_id = MS.from_organization_id
        GROUP
        BY     MS.intransit_owning_org_id,
               ITEMS.inventory_item_id,
               ITEMS.category_id,
               MS.item_revision,
               ITEMS.cost_type_id,
               CGS.cost_group_id,
               MS.from_organization_id,
               MS.to_organization_id,
               NVL(
                 MMT.intransit_account,
                 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
               );
Line: 2219

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account
                 )
          SELECT 7, -- UNCOSTED_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
                     ),
                     2,
                     MMT.primary_quantity
                   )
                 ),
                 NVL(MMT.intransit_account,MIP.intransit_inv_account)
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_TO
          WHERE  MMT.transfer_organization_id = p_organization_id
          AND    MMT.transaction_action_id = 21
          AND    MMT.costed_flag IN ('N','E')
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_cost_group_id,
                   2,MMT.cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.transfer_organization_id
          AND    MIP.from_organization_id = MMT.organization_id
          AND    MSI_TO.organization_id = MMT.transfer_organization_id
          AND    MSI_TO.inventory_item_id = MMT.inventory_item_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account,MIP.intransit_inv_account);
Line: 2307

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account
                 )
          SELECT 7, -- UNCOSTED_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.transfer_organization_id,
                 MMT.organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     MMT.primary_quantity,
                     2,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
                     )
                   )
                 ),
                 NVL(MMT.intransit_account, MIP.intransit_inv_account)
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_FROM
          WHERE  MMT.organization_id = p_organization_id
          AND    MMT.transaction_action_id = 12
          AND    MMT.costed_flag IN ('N', 'E')
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.cost_group_id,
                   2,MMT.transfer_cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.organization_id
          AND    MIP.from_organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.inventory_item_id = MMT.inventory_item_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account, MIP.intransit_inv_account);
Line: 2403

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account
                 )
          SELECT 8, -- ROLLBACK_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
                     ),
                     2,
                     MMT.primary_quantity
                   )
                 ),
                 NVL(MMT.intransit_account,MIP.intransit_inv_account)
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_TO,
		  mtl_transaction_types MTT
          WHERE  MMT.transfer_organization_id = p_organization_id
          AND    MMT.transaction_action_id = 21
          AND    MMT.costed_flag IS NULL
          AND    MMT.transaction_date > p_valuation_date
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_cost_group_id,
                   2,MMT.cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.transfer_organization_id
          AND    MIP.from_organization_id = MMT.organization_id
          AND    MSI_TO.organization_id = MMT.transfer_organization_id
          AND    MSI_TO.inventory_item_id = MMT.inventory_item_id
	  AND    MTT.transaction_action_id = MMT.transaction_action_id
 	  AND    MTT.transaction_source_type_id = MMT.transaction_source_type_id
 	  AND    MTT.transaction_type_id = MMT.transaction_type_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account,MIP.intransit_inv_account);
Line: 2500

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account
                 )
          SELECT 8, -- ROLLBACK_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.transfer_organization_id,
                 MMT.organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     MMT.primary_quantity,
                     2,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
                     )
                   )
                 ),
                 NVL(MMT.intransit_account, MIP.intransit_inv_account)
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_FROM,
		  mtl_transaction_types MTT
          WHERE  MMT.organization_id = p_organization_id
          AND    MMT.transaction_action_id = 12
          AND    MMT.costed_flag IS NULL
          AND    MMT.transaction_date > p_valuation_date
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.cost_group_id,
                   2,MMT.transfer_cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.organization_id
          AND    MIP.from_organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.inventory_item_id = MMT.inventory_item_id
	  AND    MTT.transaction_action_id = MMT.transaction_action_id
          AND    MTT.transaction_source_type_id = MMT.transaction_source_type_id
          AND    MTT.transaction_type_id = MMT.transaction_type_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account, MIP.intransit_inv_account);
Line: 2600

        INSERT
        INTO   cst_inv_qty_temp(
                 qty_source,
                 organization_id,
                 inventory_item_id,
                 category_id,
                 revision,
                 cost_type_id,
                 cost_group_id,
                 from_organization_id,
                 to_organization_id,
                 rollback_qty,
                 intransit_inv_account
               )
        SELECT 6,-- CURRENT_INTRANSIT
               MS.intransit_owning_org_id,
               ITEMS.inventory_item_id,
               ITEMS.category_id,
               MS.item_revision,
               ITEMS.cost_type_id,
               CGS.cost_group_id,
               MS.from_organization_id,
               MS.to_organization_id,
               SUM(
                 DECODE(
                   MS.intransit_owning_org_id,
                   MS.from_organization_id,
                   inv_convert.inv_um_convert(
                     MS.item_id,NULL,MS.quantity,NULL,NULL,
                     MS.unit_of_measure,MSI_FROM.primary_unit_of_measure
                   ),
                   MS.to_org_primary_quantity
                 )
               ),
               NVL(
                 MMT.intransit_account,
                 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
               )
        FROM   mtl_supply MS,
               cst_item_list_temp ITEMS,
               cst_cg_list_temp CGS,
               mtl_parameters MP,
               mtl_interorg_parameters MIP,
               mtl_material_transactions MMT,
               rcv_shipment_lines RSL,
               mtl_system_items MSI_FROM
        WHERE  MS.from_organization_id = p_organization_id
      /*  AND    MS.intransit_owning_org_id = p_organization_id */ /* Bug 5664736 */
      	AND    MS.intransit_owning_org_id = DECODE(NVL(p_own,-1),1,p_organization_id,MS.intransit_owning_org_id)
        AND    MS.item_id = ITEMS.inventory_item_id
        AND    MS.supply_type_code IN ('SHIPMENT','RECEIVING')
        AND    MS.destination_type_code = 'INVENTORY'
        AND    NVL(MS.cost_group_id,MP.default_cost_group_id) = CGS.cost_group_id
        AND    MP.organization_id = MS.intransit_owning_org_id
        AND    RSL.shipment_line_id = MS.shipment_line_id
        AND    MMT.transaction_id (+) = RSL.mmt_transaction_id
        AND    MIP.from_organization_id (+) = MS.from_organization_id
        AND    MIP.to_organization_id (+) = MS.to_organization_id
        AND    MIP.fob_point (+) =
               DECODE(
                 MS.intransit_owning_org_id,
                 MS.from_organization_id, 2,
                 MS.to_organization_id, 1)
        AND    MSI_FROM.inventory_item_id = MS.item_id
        AND    MSI_FROM.organization_id = MS.from_organization_id
        GROUP
        BY     MS.intransit_owning_org_id,
               ITEMS.inventory_item_id,
               ITEMS.category_id,
               MS.item_revision,
               ITEMS.cost_type_id,
               CGS.cost_group_id,
               MS.from_organization_id,
               MS.to_organization_id,
               NVL(
                 MMT.intransit_account,
                 NVL(MIP.intransit_inv_account,MP.intransit_inv_account)
               );
Line: 2699

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account
                 )
          SELECT 7, -- UNCOSTED_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
                     ),
                     2,
                     MMT.primary_quantity
                   )
                 ),
                 NVL(MMT.intransit_account,MIP.intransit_inv_account)
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_TO
          WHERE  MMT.organization_id = p_organization_id
          AND    MMT.transaction_action_id = 21
          AND    MMT.costed_flag IN ('N','E')
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_cost_group_id,
                   2,MMT.cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.transfer_organization_id
          AND    MIP.from_organization_id = MMT.organization_id
          AND    MSI_TO.organization_id = MMT.transfer_organization_id
          AND    MSI_TO.inventory_item_id = MMT.inventory_item_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account,MIP.intransit_inv_account);
Line: 2791

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account
                 )
          SELECT 7, -- UNCOSTED_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.transfer_organization_id,
                 MMT.organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     MMT.primary_quantity,
                     2,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
                     )
                   )
                 ),
                 NVL(MMT.intransit_account, MIP.intransit_inv_account)
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_FROM
          WHERE  MMT.transfer_organization_id = p_organization_id
          AND    MMT.transaction_action_id = 12
          AND    MMT.costed_flag IN ('N', 'E')
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.cost_group_id,
                   2,MMT.transfer_cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.organization_id
          AND    MIP.from_organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.inventory_item_id = MMT.inventory_item_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account, MIP.intransit_inv_account);
Line: 2887

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account
                 )
          SELECT 8, -- ROLLBACK_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_TO.primary_uom_code,NULL,NULL
                     ),
                     2,
                     MMT.primary_quantity
                   )
                 ),
                 NVL(MMT.intransit_account,MIP.intransit_inv_account)
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_TO,
                 mtl_transaction_types MTT
          WHERE  MMT.organization_id = p_organization_id
          AND    MMT.transaction_action_id = 21
          AND    MMT.costed_flag IS NULL
          AND    MMT.transaction_date > p_valuation_date
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_cost_group_id,
                   2,MMT.cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.transfer_organization_id
          AND    MIP.from_organization_id = MMT.organization_id
          AND    MSI_TO.organization_id = MMT.transfer_organization_id
          AND    MSI_TO.inventory_item_id = MMT.inventory_item_id
	   AND    MTT.transaction_action_id = MMT.transaction_action_id
           AND    MTT.transaction_source_type_id = MMT.transaction_source_type_id
           AND    MTT.transaction_type_id = MMT.transaction_type_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.transfer_organization_id,
                   2,MMT.organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account,MIP.intransit_inv_account);
Line: 2984

          INSERT
          INTO   cst_inv_qty_temp(
                   qty_source,
                   organization_id,
                   inventory_item_id,
                   category_id,
                   revision,
                   cost_type_id,
                   cost_group_id,
                   from_organization_id,
                   to_organization_id,
                   rollback_qty,
                   intransit_inv_account
                 )
          SELECT 8, -- ROLLBACK_INTRANSIT
                 DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.transfer_organization_id,
                 MMT.organization_id,
                 SUM(
                   DECODE(
                     NVL(MMT.fob_point,MIP.fob_point),
                     1,
                     MMT.primary_quantity,
                     2,
                     inv_convert.inv_um_convert(
                       MMT.inventory_item_id,NULL,MMT.transaction_quantity,
                       MMT.transaction_uom,MSI_FROM.primary_uom_code,NULL,NULL
                     )
                   )
                 ),
                 NVL(MMT.intransit_account, MIP.intransit_inv_account)
          FROM   mtl_material_transactions MMT,
                 cst_item_list_temp ITEMS,
                 cst_cg_list_temp CGS,
                 mtl_interorg_parameters MIP,
                 mtl_system_items MSI_FROM,
                 mtl_transaction_types MTT
          WHERE  MMT.transfer_organization_id = p_organization_id
          AND    MMT.transaction_action_id = 12
          AND    MMT.costed_flag IS NULL
          AND    MMT.transaction_date > p_valuation_date
          AND    MMT.inventory_item_id = ITEMS.inventory_item_id
          AND    DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.cost_group_id,
                   2,MMT.transfer_cost_group_id
                 ) =
                 CGS.cost_group_id
          AND    MIP.to_organization_id = MMT.organization_id
          AND    MIP.from_organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.organization_id = MMT.transfer_organization_id
          AND    MSI_FROM.inventory_item_id = MMT.inventory_item_id
          AND    MTT.transaction_action_id = MMT.transaction_action_id
 	  AND    MTT.transaction_source_type_id = MMT.transaction_source_type_id
 	  AND    MTT.transaction_type_id = MMT.transaction_type_id
          GROUP
          BY     DECODE(
                   NVL(MMT.fob_point,MIP.fob_point),
                   1,MMT.organization_id,
                   2,MMT.transfer_organization_id
                 ),
                 ITEMS.inventory_item_id,
                 ITEMS.category_id,
                 MMT.revision,
                 ITEMS.cost_type_id,
                 CGS.cost_group_id,
                 MMT.organization_id,
                 MMT.transfer_organization_id,
                 NVL(MMT.intransit_account, MIP.intransit_inv_account);
Line: 3151

    INSERT
    INTO   cst_inv_qty_temp(
             qty_source,
             organization_id,
             inventory_item_id,
             category_id,
             cost_type_id,
             rcv_transaction_id,
             revision,
             rollback_qty
           )
    SELECT 9, -- RECEIVED
           MS.to_organization_id,
           CILT.inventory_item_id,
           DECODE(MS.item_id, NULL, POL.category_id, CILT.category_id),
           CILT.cost_type_id,
           DECODE(RT.transaction_type,
            'ACCEPT', Get_ParentReceiveTxn(MS.rcv_transaction_id),
            'REJECT', Get_ParentReceiveTxn(MS.rcv_transaction_id),
            'TRANSFER', Get_ParentReceiveTxn(MS.rcv_transaction_id),
            (MS.rcv_transaction_id)),
            -- MTL_SUPPLY stores parent Match/Receive except for Accept/Reject/Transfers
           DECODE(p_qty_by_revision, 1, POL.item_revision, NULL),
           SUM(MS.to_org_primary_quantity) -- sum across po distributions
    FROM   cst_item_list_temp CILT,
           cst_cg_list_temp CCLT,
           mtl_supply MS,
           rcv_transactions RT,
           mtl_parameters MP,
           po_lines_all POL,
           po_line_locations_all POLL,
           pjm_project_parameters PPP
    WHERE  NVL(CILT.inventory_item_id, -1) = NVL(MS.item_id, -1)
    AND    MP.organization_id = MS.to_organization_id
    AND    MS.to_organization_id = p_organization_id
    AND    NVL(
             MS.cost_group_id,
             NVL(PPP.costing_group_id,MP.default_cost_group_id)
           ) = CCLT.cost_group_id
    AND    MS.supply_type_code = 'RECEIVING'
    AND    RT.transaction_id = MS.rcv_transaction_id
           -- Joining to MS eliminates consigned and drop ship receipts
    AND    NVL(RT.consigned_flag, 'N') = 'N' -- eliminate consigned
    AND    RT.source_document_code = 'PO'
    AND    POL.po_line_id = RT.po_line_id
    AND    PPP.project_id (+) = POL.project_id
    AND    POLL.line_location_id = RT.po_line_location_id
    AND    POLL.shipment_type <> 'PREPAYMENT'
    AND    POLL.matching_basis = 'QUANTITY'  -- eliminate service line types
    AND    POLL.accrue_on_receipt_flag = DECODE(p_include_period_end, 1, POLL.accrue_on_receipt_flag, 'Y')
    GROUP
    BY     MS.to_organization_id,
           CILT.inventory_item_id,
           DECODE(MS.item_id, NULL, POL.category_id, CILT.category_id),
           CILT.cost_type_id,
           DECODE(RT.transaction_type,
            'ACCEPT', Get_ParentReceiveTxn(MS.rcv_transaction_id),
            'REJECT', Get_ParentReceiveTxn(MS.rcv_transaction_id),
            'TRANSFER', Get_ParentReceiveTxn(MS.rcv_transaction_id),
            (MS.rcv_transaction_id)),
     DECODE(p_qty_by_revision, 1, POL.item_revision, NULL);
Line: 3234

      INSERT
      INTO   cst_inv_qty_temp(
               qty_source,
               organization_id,
               inventory_item_id,
               category_id,
               cost_type_id,
               rcv_transaction_id,
               revision,
               rollback_qty
             )
      SELECT 10                   qty_source, -- ROLLBACK RECEIVING
             RT.organization_id,
             CILT.inventory_item_id,
             DECODE(POL.item_id, NULL, POL.category_id, CILT.category_id),
             CILT.cost_type_id,
             DECODE(RT.transaction_type,
                    'RECEIVE', RT.transaction_id,
                    'MATCH', RT.transaction_id,
                    Get_ParentReceiveTxn(RT.transaction_id)) rcv_transaction_id,
             DECODE(p_qty_by_revision, 1, POL.item_revision, NULL),
             SUM(DECODE(RT.transaction_type,
                        'RECEIVE', -1 * RT.primary_quantity,
                        'DELIVER', 1 * RT.primary_quantity,
                        'RETURN TO RECEIVING', -1 * RT.primary_quantity,
                        'RETURN TO VENDOR', DECODE(PARENT_RT.transaction_type,
                                                   'UNORDERED', 0,
                                                   1 * RT.primary_quantity),
                        'MATCH', -1 * RT.primary_quantity,
                        'CORRECT', DECODE(PARENT_RT.transaction_type,
                                          'UNORDERED', 0,
                                          'RECEIVE', -1 * RT.primary_quantity,
                                          'DELIVER', 1 * RT.primary_quantity,
                                          'RETURN TO RECEIVING', -1 * RT.primary_quantity,
                                          'RETURN TO VENDOR', DECODE(GRPARENT_RT.transaction_type,
                                                                     'UNORDERED', 0,
                                                                     1 * RT.primary_quantity),
                                          'MATCH', -1 * RT.primary_quantity,
                                          0),
                        0)
             ) rollback_qty
     FROM    cst_item_list_temp CILT,
             cst_cg_list_temp CCLT,
             rcv_transactions RT,
             rcv_transactions PARENT_RT,
             rcv_transactions GRPARENT_RT,
             mtl_parameters MP,
             po_lines_all POL,
             po_line_locations_all POLL,
             pjm_project_parameters PPP
     WHERE   NVL(CILT.inventory_item_id, -1) = NVL(POL.item_id, -1)
     AND     MP.organization_id = RT.organization_id
     AND     RT.organization_id = p_organization_id
     AND     NVL(PPP.costing_group_id,MP.default_cost_group_id) = CCLT.cost_group_id
     AND     NVL(RT.consigned_flag, 'N') = 'N' -- eliminate consigned
     AND     NVL(RT.dropship_type_code, 3) = 3 -- eliminate drop ship
     AND     RT.transaction_date > p_valuation_date
     AND     RT.transaction_type in
               ('RECEIVE', 'DELIVER', 'RETURN TO RECEIVING', 'RETURN TO VENDOR', 'CORRECT', 'MATCH')
     AND    RT.source_document_code = 'PO'
     AND    DECODE(RT.parent_transaction_id,
                   -1, NULL,
                   0, NULL,
                   RT.parent_transaction_id) = PARENT_RT.transaction_id(+)
     AND    DECODE(PARENT_RT.parent_transaction_id,
                   -1, NULL,
                   0, NULL,
                   PARENT_RT.parent_transaction_id) = GRPARENT_RT.transaction_id(+)
     AND    POL.po_line_id = RT.po_line_id
     AND    PPP.project_id (+) = POL.project_id
     AND    POLL.line_location_id = RT.po_line_location_id
     AND    POLL.shipment_type <> 'PREPAYMENT'
     AND    POLL.matching_basis = 'QUANTITY' -- eliminate service line types
     AND    POLL.accrue_on_receipt_flag = DECODE(p_include_period_end, 1, POLL.accrue_on_receipt_flag, 'Y')
     GROUP
     BY     RT.organization_id,
            CILT.inventory_item_id,
            DECODE(POL.item_id, NULL, POL.category_id, CILT.category_id),
            CILT.cost_type_id,
            DECODE(RT.transaction_type,
                   'RECEIVE', RT.transaction_id,
                   'MATCH', RT.transaction_id,
                   Get_ParentReceiveTxn(RT.transaction_id)),
            DECODE(p_qty_by_revision, 1, POL.item_revision, NULL)
     HAVING SUM(DECODE(RT.transaction_type,
                       'RECEIVE', -1 * RT.primary_quantity,
                       'DELIVER', 1 * RT.primary_quantity,
                       'RETURN TO RECEIVING', -1 * RT.primary_quantity,
                       'RETURN TO VENDOR', DECODE(PARENT_RT.transaction_type,
                                                  'UNORDERED', 0,
                                                  1 * RT.primary_quantity),
                       'MATCH', -1 * RT.primary_quantity,
                       'CORRECT', DECODE(PARENT_RT.transaction_type,
                                         'UNORDERED', 0,
                                         'RECEIVE', -1 * RT.primary_quantity,
                                         'DELIVER', 1 * RT.primary_quantity,
                                         'RETURN TO RECEIVING', -1 * RT.primary_quantity,
                                         'RETURN TO VENDOR', DECODE(GRPARENT_RT.transaction_type,
                                                                    'UNORDERED', 0,
                                                                    1 * RT.primary_quantity),
                                         'MATCH', -1 * RT.primary_quantity,
                                         0),
                       0)
               ) <> 0;
Line: 3376

    SELECT transaction_id
    INTO   l_parent_transaction_id
    FROM (
        SELECT  RT.transaction_id transaction_id,
                RT.parent_transaction_id parent_transaction_id,
                RT.transaction_type
        FROM    rcv_transactions RT
     START WITH transaction_id  = p_rcv_transaction_id
     CONNECT BY transaction_id  = PRIOR parent_transaction_id)
    WHERE ((transaction_type = 'RECEIVE' and parent_transaction_id=-1)
    OR    transaction_type = 'MATCH');
Line: 3406

    l_min_cost_update_id NUMBER(15);
Line: 3407

    l_max_cost_update_id NUMBER(15);
Line: 3418

      SELECT DISTINCT
             CIQT.organization_id,
             CIQT.inventory_item_id,
             CIQT.cost_type_id
      FROM   cst_inv_qty_temp CIQT,
             mtl_parameters MP
      WHERE  CIQT.organization_id = p_organization_id
      AND    MP.organization_id = p_organization_id
      AND    MP.primary_cost_method = 1
      AND    CIQT.qty_source NOT IN (1,2,9,10) -- PRIOR SUMMARY, CURRENT SUMMARY,
                                               -- RECEIVING, PAST RECEIVING
      AND    CIQT.cost_type_id IS NOT NULL;  -- bug 6893581
Line: 3442

      SELECT DISTINCT
             CIQT.organization_id,
             CIQT.inventory_item_id,
             CIQT.rcv_transaction_id
      FROM   cst_inv_qty_temp CIQT
      WHERE  CIQT.qty_source in (9,10);
Line: 3458

   SELECT mcacd_txn,
          mmt_txn,
          mmt_cost,
          mcacd_cost,
          TXN_DATE,
          prior_COST ,
          act,
          material_cost,
          material_overhead_cost,
          resource_cost,
          outside_processing_cost,
          overhead_cost
     FROM
          (SELECT MCACD.TRANSACTION_ID                                             mcacd_txn,
                  MMT.transaction_id                                               mmt_txn,
                  NVL(MMT.actual_cost,0)                                           mmt_cost,
                  MMT.transaction_action_id                                        act,
                  NVL(MMT.prior_COST,0)                                            prior_cost,
                  SUM(NVL(mcacd.actual_cost,0))
				               OVER (PARTITION BY MMT.transaction_id)              mcacd_cost,
                  SUM(DECODE(mcacd.cost_element_id,1,NVL(mcacd.actual_cost,0),0))
                               OVER (PARTITION BY MMT.transaction_id)              material_cost,
                  SUM(DECODE(mcacd.cost_element_id,2,NVL(mcacd.actual_cost,0),0))
                               OVER (PARTITION BY MMT.transaction_id)              material_overhead_cost,
                  SUM(DECODE(mcacd.cost_element_id,3,NVL(mcacd.actual_cost,0),0))
                               OVER (PARTITION BY MMT.transaction_id)              resource_cost,
                  SUM(DECODE(mcacd.cost_element_id,4,nvl(mcacd.actual_cost,0),0))
                               OVER (PARTITION BY MMT.transaction_id)              outside_processing_cost,
                  SUM(DECODE(mcacd.cost_element_id,5,nvl(mcacd.actual_cost,0),0))
                               OVER (PARTITION BY MMT.transaction_id)              overhead_cost,
                  NVL(MCACD.creation_date,MMT.creation_date)                       txn_date
           FROM   MTL_CST_ACTUAL_COST_DETAILS MCACD,
                  mtl_material_transactions   mmt
           WHERE  MCACD.ORGANIZATION_ID(+)   = c_organization_id
             AND  MCACD.inventory_item_id(+) = c_inventory_item_id
             AND  MMT.transaction_date       > c_valuation_date
             AND  mmt.transaction_action_id  NOT IN  (5,30,40,41,42,43,50,51,52,15,22,11,17,10,13,9,14,7,26,36,25,56,57)
             AND  NOT (mmt.transaction_action_id IN (2,28,55,3) AND mmt.primary_quantity > 0)
                  --
                  -- Standard update only originated by standard cost update avoid PAC cost update
                  --
             AND  NOT (mmt.transaction_action_id = 24 AND mmt.transaction_source_type_id <> 11)
             AND  MMT.inventory_item_id      = c_inventory_item_id
             AND  MMT.organization_id        = c_organization_id
             AND  mmt.transaction_id         = mcacd.transaction_id (+) )
          ORDER BY   TXN_DATE asc,
                     mmt_txn asc;
Line: 3523

       INSERT INTO cst_inv_cost_temp(
                     organization_id,
                     inventory_item_id,
                     cost_type_id,
                     cost_source,
                     inventory_asset_flag,
                     item_cost,
                     material_cost,
                     material_overhead_cost,
                     resource_cost,
                     outside_processing_cost,
                     overhead_cost
                   ) VALUES (
                     p_rec.organization_id,
                     p_rec.inventory_item_id,
                     p_rec.cost_type_id,
                     p_rec.cost_source,
                     p_rec.inventory_asset_flag,
                     p_rec.item_cost,
                     p_rec.material_cost,
                     p_rec.material_overhead_cost,
                     p_rec.resource_cost,
                     p_rec.outside_processing_cost,
                     p_rec.overhead_cost);
Line: 3596

        INSERT
        INTO   cst_inv_cost_temp(
                 organization_id,
                 inventory_item_id,
                 cost_type_id,
                 cost_source,
                 inventory_asset_flag,
                 item_cost,
                 material_cost,
                 material_overhead_cost,
                 resource_cost,
                 outside_processing_cost,
                 overhead_cost
               )
        SELECT l_organization_id,
               l_inventory_item_id,
               l_cost_type_id,
               1, -- CURRENT
               CIC.inventory_asset_flag,
               NVL(CIC.item_cost,0),
               NVL(CIC.material_cost,0),
               NVL(CIC.material_overhead_cost,0),
               NVL(CIC.resource_cost,0),
               NVL(CIC.outside_processing_cost,0),
               NVL(CIC.overhead_cost,0)
        FROM   mtl_parameters MP,
               cst_item_costs CIC
        WHERE  MP.organization_id = l_organization_id
        AND    CIC.organization_id = MP.cost_organization_id
        AND    CIC.inventory_item_id = l_inventory_item_id
        AND    CIC.cost_type_id = l_cost_type_id;
Line: 3646

      INSERT
      INTO   cst_inv_cost_temp(
               organization_id,
               inventory_item_id,
               cost_group_id,
               cost_type_id,
               cost_source,
               inventory_asset_flag,
               item_cost,
               material_cost,
               material_overhead_cost,
               resource_cost,
               outside_processing_cost,
               overhead_cost
             )
      SELECT CIQT.organization_id,
             CIQT.inventory_item_id,
             CIQT.cost_group_id,
             CIQT.cost_type_id,
             1, -- CURRENT
             CIC.inventory_asset_flag,
             DECODE(
               CIQT.cost_type_id,
               MP.primary_cost_method,
               NVL(CQL.item_cost,0),
               NVL(CIC.item_cost,0)
             ),
             DECODE(
               CIQT.cost_type_id,
               MP.primary_cost_method,
               NVL(CQL.material_cost,0),
               NVL(CIC.material_cost,0)
             ),
             DECODE(
               CIQT.cost_type_id,
               MP.primary_cost_method,
               NVL(CQL.material_overhead_cost,0),
               NVL(CIC.material_overhead_cost,0)
             ),
             DECODE(
               CIQT.cost_type_id,
               MP.primary_cost_method,
               NVL(CQL.resource_cost,0),
               NVL(CIC.resource_cost,0)
             ),
             DECODE(
               CIQT.cost_type_id,
               MP.primary_cost_method,
               NVL(CQL.outside_processing_cost,0),
               NVL(CIC.outside_processing_cost,0)
             ),
             DECODE(
               CIQT.cost_type_id,
               MP.primary_cost_method,
               NVL(CQL.overhead_cost,0),
               NVL(CIC.overhead_cost,0)
             )
      FROM   (
               SELECT DISTINCT
                      organization_id,
                      inventory_item_id,
                      cost_group_id,
                      cost_type_id
               FROM   cst_inv_qty_temp
               WHERE  qty_source NOT IN (1,2,9,10)
             ) CIQT,
             cst_quantity_layers CQL,
             cst_item_costs CIC,
             mtl_parameters MP
      WHERE  CIC.organization_id = CIQT.organization_id
      AND    CIC.inventory_item_id = CIQT.inventory_item_id
      AND    CIC.cost_type_id = CIQT.cost_type_id
      AND    MP.organization_id = CIQT.organization_id
      AND    MP.primary_cost_method <> 1
      AND    CQL.organization_id (+) = CIQT.organization_id
      AND    CQL.inventory_item_id (+) = CIQT.inventory_item_id
      AND    CQL.cost_group_id (+) = CIQT.cost_group_id
             -- Outer join on CQL to insert zero costs for expense
             -- items and asset items that do not have a layer
      AND    CIQT.organization_id = p_organization_id;
Line: 3765

        l_min_cost_update_id := NULL;
Line: 3766

        l_max_cost_update_id := NULL;
Line: 3784

           INSERT  INTO   cst_inv_cost_temp
		   (      organization_id,
                  inventory_item_id,
                  cost_type_id,
                  cost_source,
                  inventory_asset_flag,
                  item_cost,
                  material_cost,
                  material_overhead_cost,
                  resource_cost,
                  outside_processing_cost,
                  overhead_cost
                )
           SELECT l_organization_id,
                  l_inventory_item_id,
                  l_cost_type_id,
                  1, -- CURRENT
                  CIC.inventory_asset_flag,
                  NVL(CIC.item_cost,0),
                  NVL(CIC.material_cost,0),
                  NVL(CIC.material_overhead_cost,0),
                  NVL(CIC.resource_cost,0),
                  NVL(CIC.outside_processing_cost,0),
                  NVL(CIC.overhead_cost,0)
           FROM   mtl_parameters MP,
                  cst_item_costs CIC
           WHERE  MP.organization_id    = l_organization_id
           AND    CIC.organization_id   = MP.cost_organization_id
           AND    CIC.inventory_item_id = l_inventory_item_id
           AND    CIC.cost_type_id      = l_cost_type_id;
Line: 3837

       SELECT nvl(CIC.inventory_asset_flag,2)
         INTO l_exp_item_flag
	     FROM mtl_parameters MP,
              cst_item_costs CIC
        WHERE MP.organization_id    = l_organization_id
          AND CIC.organization_id   = MP.cost_organization_id
          AND CIC.inventory_item_id = l_inventory_item_id
          AND CIC.cost_type_id      = 1;
Line: 3885

           INSERT INTO   cst_inv_cost_temp(
                     organization_id,
                     inventory_item_id,
                     cost_type_id,
                     cost_source,
                     inventory_asset_flag,
                     item_cost,
                     material_cost,
                     material_overhead_cost,
                     resource_cost,
                     outside_processing_cost,
                     overhead_cost
                   )
            SELECT l_organization_id,
                   l_inventory_item_id,
                   1,
                   2, -- PAST
                   CIC.inventory_asset_flag,
                   SUM(NVL(CIC.item_cost,0)),
                   SUM(NVL(CIC.material_cost,0)),
                   SUM(NVL(CIC.material_overhead_cost,0)),
                   SUM(NVL(CIC.resource_cost,0)),
                   SUM(NVL(CIC.outside_processing_cost,0)),
                   SUM(NVL(CIC.overhead_cost,0))
            FROM   cst_item_costs CIC,
                   mtl_parameters MP
            WHERE  CIC.cost_type_id      = 1
            AND    MP.organization_id    = l_organization_id
            AND    CIC.organization_id   = MP.cost_organization_id
            AND    CIC.inventory_item_id = l_inventory_item_id
            GROUP BY CIC.inventory_asset_flag;
Line: 3923

             l_rec.inventory_asset_flag    := 1; --Standard cost update is only done for asset items
Line: 3963

        SELECT MIN(CSC.cost_update_id)
        INTO l_min_cost_update_id
        FROM   cst_standard_costs CSC,
               mtl_parameters MP
        WHERE  MP.organization_id = l_organization_id
        AND    CSC.organization_id = MP.cost_organization_id
        AND    CSC.inventory_item_id = l_inventory_item_id
        AND    CSC.standard_cost_revision_date > p_valuation_date;
Line: 3978

        IF l_min_cost_update_id IS NOT NULL
        THEN
          -- Yes. Figure out the prior cost update

          SELECT MAX(CSC.cost_update_id)
          INTO   l_max_cost_update_id
          FROM   cst_standard_costs CSC,
                 mtl_parameters MP
          WHERE  MP.organization_id = l_organization_id
          AND    CSC.organization_id = MP.cost_organization_id
          AND    CSC.inventory_item_id = l_inventory_item_id
          AND    CSC.standard_cost_revision_date <= p_valuation_date;
Line: 3991

          IF l_max_cost_update_id IS NOT NULL
          THEN
            -- Use the cost in cst_elemental_costs.
            INSERT
            INTO   cst_inv_cost_temp(
                     organization_id,
                     inventory_item_id,
                     cost_type_id,
                     cost_source,
                     inventory_asset_flag,
                     item_cost,
                     material_cost,
                     material_overhead_cost,
                     resource_cost,
                     outside_processing_cost,
                     overhead_cost
                   )
            SELECT l_organization_id,
                   l_inventory_item_id,
                   1,
                   2, -- PAST
                   1, -- Standard cost update is only done for asset items
                   SUM(NVL(CEC.standard_cost,0)),
                   SUM(DECODE(CEC.cost_element_id,1,NVL(CEC.standard_cost,0),0)),
                   SUM(DECODE(CEC.cost_element_id,2,NVL(CEC.standard_cost,0),0)),
                   SUM(DECODE(CEC.cost_element_id,3,NVL(CEC.standard_cost,0),0)),
                   SUM(DECODE(CEC.cost_element_id,4,NVL(CEC.standard_cost,0),0)),
                   SUM(DECODE(CEC.cost_element_id,5,NVL(CEC.standard_cost,0),0))
            FROM   cst_elemental_costs CEC,
                   mtl_parameters MP
            WHERE  CEC.cost_update_id = l_max_cost_update_id
            AND    MP.organization_id = l_organization_id
            AND    CEC.organization_id = MP.cost_organization_id
            AND    CEC.inventory_item_id = l_inventory_item_id;
Line: 4032

                                l_inventory_item_id || ' Cost Update ID '||
                                l_max_cost_update_id
              );
Line: 4039

            INSERT
            INTO   cst_inv_cost_temp(
                     organization_id,
                     inventory_item_id,
                     cost_type_id,
                     cost_source,
                     inventory_asset_flag,
                     item_cost,
                     material_cost,
                     material_overhead_cost,
                     resource_cost,
                     outside_processing_cost,
                     overhead_cost
                   )
            SELECT l_organization_id,
                   l_inventory_item_id,
                   1,
                   2, -- PAST
                   1, -- Standard cost update is only done for asset items
                   0,
                   0,
                   0,
                   0,
                   0,
                   0
            FROM   dual;
Line: 4076

          END IF; --l_max_cost_update_id
Line: 4080

          INSERT
          INTO   cst_inv_cost_temp(
                   organization_id,
                   inventory_item_id,
                   cost_type_id,
                   cost_source,
                   inventory_asset_flag,
                   item_cost,
                   material_cost,
                   material_overhead_cost,
                   resource_cost,
                   outside_processing_cost,
                   overhead_cost
                 )
          SELECT l_organization_id,
                 l_inventory_item_id,
                 1,
                 2, -- PAST
                 CIC.inventory_asset_flag,
                 NVL(CIC.item_cost,0),
                 NVL(CIC.material_cost,0),
                 NVL(CIC.material_overhead_cost,0),
                 NVL(CIC.resource_cost,0),
                 NVL(CIC.outside_processing_cost,0),
                 NVL(CIC.overhead_cost,0)
          FROM   mtl_parameters MP,
                 cst_item_costs CIC
          WHERE  MP.organization_id = l_organization_id
          AND    CIC.organization_id = MP.cost_organization_id
          AND    CIC.inventory_item_id = l_inventory_item_id
          AND    CIC.cost_type_id = 1;
Line: 4122

        END IF;      --l_min_cost_update_id
Line: 4154

      INSERT
      INTO   cst_inv_cost_temp(
               organization_id,
               inventory_item_id,
               cost_group_id,
               cost_type_id,
               cost_source,
               inventory_asset_flag,
               item_cost,
               material_cost,
               material_overhead_cost,
               resource_cost,
               outside_processing_cost,
               overhead_cost
             )
      SELECT DISTINCT
             CIQT.organization_id,
             CIQT.inventory_item_id,
             CIQT.cost_group_id,
             CIQT.cost_type_id,
             2, -- PAST
             2, -- EXPENSE
             0,
             0,
             0,
             0,
             0,
             0
      FROM   cst_inv_qty_temp CIQT,
             mtl_parameters MP,
             cst_item_costs CIC
      WHERE  MP.organization_id = CIQT.organization_id
      AND    MP.primary_cost_method <> 1
      AND    CIC.organization_id = CIQT.organization_id
      AND    CIC.inventory_item_id = CIQT.inventory_item_id
      AND    CIC.cost_type_id = CIQT.cost_type_id
      AND    CIC.inventory_asset_flag <> 1
      AND    CIQT.qty_source NOT IN (1,2,9,10) -- PRIOR SUMMARY, CURRENT SUMMARY, RECEIVING, PAST RECEIVING
      AND    CIQT.organization_id = p_organization_id;
Line: 4205

      SELECT primary_cost_method
      INTO   l_cost_method
      FROM   mtl_parameters
      WHERE  organization_id = p_organization_id;
Line: 4211

      INSERT
      INTO   cst_inv_cost_temp(
               organization_id,
               inventory_item_id,
               cost_group_id,
               cost_type_id,
               cost_source,
               inventory_asset_flag,
               item_cost,
               material_cost,
               material_overhead_cost,
               resource_cost,
               outside_processing_cost,
               overhead_cost
             )
      SELECT /*+ ORDERED use_nl(CQL,MCACD) use_hash(TFR_TXN,OWN_TXN) */
             CIQT.organization_id,
             CIQT.inventory_item_id,
             CIQT.cost_group_id,
             CIQT.primary_cost_method,
             2, -- PAST
             1, -- ASSET
             SUM(NVL(MCACD.prior_cost,0)),
             SUM(
               DECODE(MCACD.cost_element_id,1,NVL(MCACD.prior_cost,0),0)
             ),
             SUM(
               DECODE(MCACD.cost_element_id,2,NVL(MCACD.prior_cost,0),0)
             ),
             SUM(
               DECODE(MCACD.cost_element_id,3,NVL(MCACD.prior_cost,0),0)
             ),
             SUM(
               DECODE(MCACD.cost_element_id,4,NVL(MCACD.prior_cost,0),0)
             ),
             SUM(
               DECODE(MCACD.cost_element_id,5,NVL(MCACD.prior_cost,0),0)
             )
      FROM   (
               SELECT /*+ no_merge */ DISTINCT
                      CIQT.organization_id,
                      CIQT.inventory_item_id,
                      CIQT.cost_group_id,
                      MP.primary_cost_method
               FROM   cst_inv_qty_temp CIQT,
                      mtl_parameters MP
               WHERE  MP.organization_id = CIQT.organization_id
               AND    MP.primary_cost_method <> 1
               AND    CIQT.qty_source NOT IN (1,2,9,10)
                      -- PRIOR SUMMARY, CURRENT SUMMARY, RECEIVING, PAST RECEIVING
               AND    NOT EXISTS (
                        SELECT 1
                        FROM   cst_inv_cost_temp CICT
                        WHERE  CICT.organization_id = CIQT.organization_id
                        AND    CICT.inventory_item_id = CIQT.inventory_item_id
                        AND    CICT.cost_source = 2
                      )
               AND    CIQT.organization_id = p_organization_id
             ) CIQT,
             cst_quantity_layers CQL,
             (
               SELECT /*+ ORDERED use_hash(MMT,MIP) swap_join_inputs(MIP) */
                      MIN(MMT.transaction_id) transaction_id,
                      MMT.transaction_date,
                      MMT.transfer_organization_id,
                      MMT.inventory_item_id,
                      MMT.transfer_cost_group_id,
                      'N' restrict_mcacd
               FROM
                      (
                        SELECT /*+ no_merge leading(MIP) use_hash(MMT)*/
                               MIN(MMT.transaction_date) transaction_date,
                               MMT.transfer_organization_id,
                               MMT.inventory_item_id,
                               MMT.transfer_cost_group_id
                        FROM   mtl_material_transactions MMT,
                               mtl_interorg_parameters MIP
                        WHERE  MMT.transaction_action_id = 21
                        AND    MIP.from_organization_id = MMT.organization_id
                        AND    MIP.to_organization_id = MMT.transfer_organization_id
                        AND    NVL(MMT.fob_point,MIP.fob_point)=1
                        AND    MMT.costed_flag IS NULL
                        AND    MMT.transaction_date > p_valuation_date
                        AND    MMT.transfer_organization_id = p_organization_id
                        GROUP
                        BY     MMT.transfer_organization_id,
                               MMT.inventory_item_id,
                               MMT.transfer_cost_group_id
                      ) MINDATE,
                      mtl_material_transactions MMT,
                      mtl_interorg_parameters MIP
               WHERE  MMT.transfer_organization_id = MINDATE.transfer_organization_id
               AND    MMT.inventory_item_id = MINDATE.inventory_item_id
               AND    MMT.transfer_cost_group_id = MINDATE.transfer_cost_group_id
               AND    MMT.transaction_date = MINDATE.transaction_date
               AND    MMT.transaction_action_id = 21
               AND    MIP.from_organization_id = MMT.organization_id
               AND    MIP.to_organization_id = MMT.transfer_organization_id
               AND    NVL(MMT.fob_point,MIP.fob_point)=1
               AND    MMT.costed_flag IS NULL
               AND    MMT.transaction_date > p_valuation_date
               AND    MMT.transfer_organization_id = p_organization_id
               GROUP
               BY     MMT.transaction_date,
                      MMT.transfer_organization_id,
                      MMT.inventory_item_id,
                      MMT.transfer_cost_group_id
             ) TFR_TXN,
             (
               SELECT /*+ leading(MINDATE) use_nl(MMT) index(MMT,MTL_MATERIAL_TRANSACTIONS_N1) */
                      MIN(MMT.transaction_id) transaction_id,
                      MMT.transaction_date,
                      MMT.organization_id,
                      MMT.inventory_item_id,
                      /* In average costing organizations, common issue to WIP
                         results in reaveraging of the item cost in the transfer
                         cost group */
                      DECODE(
                        l_cost_method,
                        2,
                        DECODE(
                          MMT.transaction_action_id,
                          1,
                          DECODE(
                            MMT.transaction_source_type_id,
                            5,
                            NVL(MMT.TRANSFER_COST_GROUP_ID, MMT.cost_group_id),
                            MMT.cost_group_id
                          ),
                          MMT.cost_group_id
                        ),
                        MMT.cost_group_id
                      ) cost_group_id,
                      /* For common issue to WIP transactions in average costing
                         organizations, we insert rows both for the cost group
                         transfer and issue to WIP. We are only interested in the
                         rows corresponding to the cost group transfer. The
                         restrict_mcacd flag is used to determine if we need to
                         check on MCACD.transaction_action_id */
                      DECODE(
                        l_cost_method,
                        2,
                        DECODE(
                          MMT.transaction_action_id,
                          1,
                          DECODE(
                            MMT.transaction_source_type_id,
                            5,
                            DECODE(
                              MMT.transfer_cost_group_id,
                              NULL,
                              'N',
                              /* Bug 3500534
                              It is possible to have normal issue to WIP transactions in
                              average costing organizations with transfer_cost_group_id
                              = cost_group_id.  Adding the following condition for
                              cost_group_id ensures such cases are handled as normal issue
                              to WIP rather than common. */
                              MMT.cost_group_id,
                              'N',
                              'Y'
                            ),
                            'N'
                          ),
                          'N'
                        ),
                        'N'
                      ) restrict_mcacd
               FROM   mtl_material_transactions MMT,
                      (
                        SELECT /*+ no_merge */ MIN(MMT.transaction_date) transaction_date,
                               MMT.organization_id,
                               MMT.inventory_item_id,
                               DECODE(
                                 l_cost_method,
                                 2,
                                 DECODE(
                                   MMT.transaction_action_id,
                                   1,
                                   DECODE(
                                     MMT.transaction_source_type_id,
                                     5,
                                     NVL(MMT.TRANSFER_COST_GROUP_ID, MMT.cost_group_id),
                                     MMT.cost_group_id
                                   ),
                                   MMT.cost_group_id
                                 ),
                                 MMT.cost_group_id
                               ) cost_group_id
                        FROM   mtl_material_transactions MMT
                        WHERE  MMT.transaction_action_id <> 30
                        AND    MMT.prior_cost IS NOT NULL
                        AND    MMT.costed_flag IS NULL
                               -- Ignore consigned transactions
                        AND    MMT.organization_id =
                               NVL(MMT.owning_organization_id, MMT.organization_id)
                        AND    NVL(MMT.owning_tp_type,2) = 2
                        AND    NVL(MMT.logical_transaction,-1) <> 1
                        AND    MMT.transaction_date > p_valuation_date
                        AND    MMT.organization_id = p_organization_id
                        GROUP
                        BY     MMT.organization_id,
                               MMT.inventory_item_id,
                               DECODE(
                                 l_cost_method,
                                 2,
                                 DECODE(
                                   MMT.transaction_action_id,
                                   1,
                                   DECODE(
                                     MMT.transaction_source_type_id,
                                     5,
                                     NVL(MMT.TRANSFER_COST_GROUP_ID, MMT.cost_group_id),
                                     MMT.cost_group_id
                                   ),
                                   MMT.cost_group_id
                                 ),
                                 MMT.cost_group_id
                               )
                      ) MINDATE
               WHERE  MMT.organization_id = MINDATE.organization_id
               AND    MMT.inventory_item_id = MINDATE.inventory_item_id
               AND    DECODE(
                        l_cost_method,
                        2,
                        DECODE(
                          MMT.transaction_action_id,
                          1,
                          DECODE(
                            MMT.transaction_source_type_id,
                            5,
                            NVL(MMT.transfer_cost_group_id, MMT.cost_group_id),
                            MMT.cost_group_id
                          ),
                          MMT.cost_group_id
                        ),
                        MMT.cost_group_id
                      ) = MINDATE.cost_group_id
               AND    MMT.transaction_date = MINDATE.transaction_date
               AND    MMT.transaction_action_id <> 30
               AND    MMT.prior_cost IS NOT NULL
               AND    MMT.costed_flag IS NULL
                      -- Ignore consigned transactions
               AND    MMT.organization_id =
                      NVL(MMT.owning_organization_id, MMT.organization_id)
               AND    NVL(MMT.owning_tp_type,2) = 2
               AND    NVL(MMT.logical_transaction,-1) <> 1
               AND    MMT.organization_id = p_organization_id
               GROUP
               BY     MMT.transaction_date,
                      MMT.organization_id,
                      MMT.inventory_item_id,
                      DECODE(
                        l_cost_method,
                        2,
                        DECODE(
                          MMT.transaction_action_id,
                          1,
                          DECODE(
                            MMT.transaction_source_type_id,
                            5,
                            NVL(MMT.transfer_cost_group_id, MMT.cost_group_id),
                            MMT.cost_group_id
                          ),
                          MMT.cost_group_id
                        ),
                        MMT.cost_group_id
                      ),
                      DECODE(
                        l_cost_method,
                        2,
                        DECODE(
                          MMT.transaction_action_id,
                          1,
                          DECODE(
                            MMT.transaction_source_type_id,
                            5,
                            DECODE(
                              MMT.transfer_cost_group_id,
                              NULL,
                              'N',
                              /* Bug 3500534
                              It is possible to have normal issue to WIP transactions in
                              average costing organizations with transfer_cost_group_id
                              = cost_group_id.  Adding the following condition for
                              cost_group_id ensures such cases are handled as normal issue
                              to WIP rather than common. */
                              MMT.cost_group_id,
                              'N',
                              'Y'
                            ),
                            'N'
                          ),
                          'N'
                        ),
                        'N'
                      )
             ) OWN_TXN,
             mtl_cst_actual_cost_details MCACD
      WHERE  MCACD.transaction_id = DECODE(
                                      NVL(TFR_TXN.transaction_id,-1),
                                      -1,
                                      OWN_TXN.transaction_id,
                                      DECODE(
                                        NVL(OWN_TXN.transaction_id,-1),
                                        -1,
                                        TFR_TXN.transaction_id,
                                        DECODE(
                                          TFR_TXN.transaction_date,
                                          OWN_TXN.transaction_date,
                                          LEAST(
                                            TFR_TXN.transaction_id,
                                            OWN_TXN.transaction_id
                                          ),
                                          DECODE(
                                            LEAST(
                                              TFR_TXN.transaction_date,
                                              OWN_TXN.transaction_date
                                            ),
                                            TFR_TXN.transaction_date,
                                            TFR_TXN.transaction_id,
                                            OWN_TXN.transaction_id
                                          )
                                        )
                                      )
                                    )
      AND    MCACD.layer_id = CQL.layer_id
      AND    MCACD.organization_id = p_organization_id
      AND    MCACD.transaction_action_id =
             DECODE(
               MCACD.transaction_id,
               OWN_TXN.transaction_id,
               DECODE(OWN_TXN.restrict_mcacd, 'Y', 2, MCACD.transaction_action_id),
               MCACD.transaction_action_id
             )
      AND    CQL.organization_id = CIQT.organization_id
      AND    CQL.cost_group_id = CIQT.cost_group_id
      AND    CQL.inventory_item_id = CIQT.inventory_item_id
      AND    TFR_TXN.transfer_organization_id (+) = CIQT.organization_id
      AND    TFR_TXN.transfer_cost_group_id (+) = CIQT.cost_group_id
      AND    TFR_TXN.inventory_item_id (+) = CIQT.inventory_item_id
      AND    OWN_TXN.organization_id (+) = CIQT.organization_id
      AND    OWN_TXN.cost_group_id (+) = CIQT.cost_group_id
      AND    OWN_TXN.inventory_item_id (+) = CIQT.inventory_item_id
      GROUP
      BY     CIQT.organization_id,
             CIQT.inventory_item_id,
             CIQT.cost_group_id,
             CIQT.primary_cost_method;
Line: 4572

      INSERT
      INTO   cst_inv_cost_temp(
               organization_id,
               inventory_item_id,
               cost_group_id,
               cost_type_id,
               cost_source,
               inventory_asset_flag,
               item_cost,
               material_cost,
               material_overhead_cost,
               resource_cost,
               outside_processing_cost,
               overhead_cost
             )
      SELECT CIQT.organization_id,
             CIQT.inventory_item_id,
             CIQT.cost_group_id,
             CIQT.primary_cost_method,
             2, -- PAST
             1, -- ASSET
             NVL(CQL.item_cost,0),
             NVL(CQL.material_cost,0),
             NVL(CQL.material_overhead_cost,0),
             NVL(CQL.resource_cost,0),
             NVL(CQL.outside_processing_cost,0),
             NVL(CQL.overhead_cost,0)
      FROM   cst_quantity_layers CQL,
             (
               SELECT DISTINCT
                      CIQT.organization_id,
                      CIQT.inventory_item_id,
                      CIQT.cost_group_id,
                      MP.primary_cost_method
               FROM   cst_inv_qty_temp CIQT,
                      mtl_parameters MP
               WHERE  MP.organization_id = CIQT.organization_id
               AND    MP.primary_cost_method <> 1
               AND    CIQT.qty_source NOT IN (1,2,9,10)
                      -- PRIOR SUMMARY, CURRENT SUMMARY, RECEIVING, PAST RECEIVING
               AND    CIQT.organization_id = p_organization_id
             ) CIQT
      WHERE  CQL.organization_id (+) = CIQT.organization_id
      AND    CQL.inventory_item_id (+) = CIQT.inventory_item_id
      AND    CQL.cost_group_id (+) = CIQT.cost_group_id
      /* The outer join above is for asset items that do not have a layer */
      AND    NOT EXISTS (
               SELECT 1
               FROM   cst_inv_cost_temp CICT
               WHERE  CICT.organization_id = CIQT.organization_id
               AND    CICT.inventory_item_id = CIQT.inventory_item_id
               AND    CICT.cost_group_id = CIQT.cost_group_id
               AND    CICT.cost_source = 2
             );
Line: 4682

        INSERT
        INTO   cst_inv_cost_temp(
                 cost_source,
                 organization_id,
                 inventory_item_id,
                 rcv_transaction_id,
                 item_cost
               )
        SELECT l_rcv_cost_source,
               l_organization_id,
               l_inventory_item_id,
               l_rcv_transaction_id,
               l_receiving_cost
        FROM   dual;