APPS.CST_MGD_INFL_ADJUSTMENT_PVT SQL Statements

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

Line: 12

, last_update_date         DATE
, last_updated_by          NUMBER
, creation_date            DATE
, created_by               NUMBER
, last_update_login        NUMBER
, request_id               NUMBER
, program_application_id   NUMBER
, program_id               NUMBER
, program_update_date      DATE
, country_code             VARCHAR2(2)
, transfer_organization_id NUMBER
, entered_dr               NUMBER
, entered_cr               NUMBER
);

Line: 73

  SELECT
    Period_Close_Date
  INTO
    l_period_close_date
  FROM
    ORG_ACCT_PERIODS
  WHERE Organization_ID = p_org_id
    AND Acct_Period_ID  = p_acct_period_id
    AND Open_Flag       = 'N'
    AND Period_Close_Date IS NOT NULL;

Line: 144

  SELECT
    Schedule_Close_Date
  , Acct_Period_ID
  INTO
    x_prev_sch_close_date
  , x_prev_acct_period_id
  FROM
    ORG_ACCT_PERIODS
  WHERE Organization_ID = p_organization_id
    AND Acct_Period_ID  = (SELECT
                             MAX(Acct_Period_ID)
                           FROM
                             CST_MGD_INFL_ADJ_PER_STATUSES
                           WHERE Organization_ID = p_organization_id
                             AND Status          = 'FINAL')
    AND Open_Flag       = 'N'
    AND Period_Close_Date IS NOT NULL;

Line: 207

  SELECT oap2.period_year * 10000 + oap2.period_num
  INTO   l_cur_org_acct_period_val
  FROM   ORG_ACCT_PERIODS oap2
  WHERE  oap2.organization_id = p_organization_id
  AND    oap2.acct_period_id  = p_acct_period_id;

Line: 213

  SELECT oap.acct_period_id
  INTO   l_prev_org_acct_period_id
  FROM   ORG_ACCT_PERIODS oap
  WHERE  oap.period_year * 10000 + oap.period_num =
         (SELECT MAX(oap2.period_year * 10000 + oap2.period_num)
          FROM   ORG_ACCT_PERIODS oap2
          WHERE  oap2.organization_id = p_organization_id
          AND    (oap2.period_year * 10000 + oap2.period_num) <
                 l_cur_org_acct_period_val
         )
  AND    oap.organization_id = p_organization_id;

Line: 269

  SELECT
    Period_Start_Date
  , Schedule_Close_Date
  INTO
    x_curr_period_start_date
  , x_curr_period_end_date
  FROM
    ORG_ACCT_PERIODS
  WHERE Acct_Period_ID  = p_acct_period_id
    AND Organization_ID = p_org_id;

Line: 356

  SELECT
    NVL((Actual_Inflation_Adj - ABS(Issue_Inflation_Adj)),0)
  , NVL((Actual_Qty - ABS(Issue_Qty)),0)
  , NVL((Actual_Cost - ABS(Issue_Cost)),0)
  INTO
    l_previous_inflation_adj
  , l_previous_qty
  , l_previous_cost
  FROM
    CST_MGD_INFL_ADJUSTED_COSTS
  WHERE Country_Code      = nvl(p_country_code, country_code)
    AND Acct_Period_ID    = p_prev_acct_period_id
    AND Organization_ID   = p_organization_id
    AND Inventory_Item_ID = p_inventory_item_id;

Line: 424

      SELECT   NVL(SUM(period_end_quantity),0)
             , NVL(SUM(period_end_unit_cost),0)
      INTO     l_previous_qty
             , l_previous_unit_cost
      FROM   CST_PER_CLOSE_DTLS_V
      WHERE  organization_id   = p_organization_id
      AND    inventory_item_id = p_inventory_item_id
      AND    acct_period_id    = l_prev_org_acct_period_id
      AND    cost_group_id     = NVL(p_cost_group_id,cost_group_id); */

Line: 434

      SELECT NVL(SUM(period_end_quantity),0)
             , NVL(SUM(period_end_unit_cost*period_end_quantity),0)
	INTO     l_previous_qty
             , l_previous_cost
	FROM (
	SELECT  rollback_quantity period_end_quantity,
		decode(rollback_quantity,0,0,rollback_value/rollback_quantity) period_end_unit_cost
	  FROM    cst_period_close_summary
	  WHERE organization_id   = p_organization_id
	      AND    inventory_item_id = p_inventory_item_id
	      AND    acct_period_id    = l_prev_org_acct_period_id
	      AND    cost_group_id     = NVL(p_cost_group_id,cost_group_id)
	UNION ALL
	SELECT  period_end_quantity, period_end_unit_cost
	  FROM    mtl_per_close_dtls
	  WHERE organization_id   = p_organization_id
	      AND    inventory_item_id = p_inventory_item_id
	      AND    acct_period_id    = l_prev_org_acct_period_id
	      AND    cost_group_id     = NVL(p_cost_group_id,cost_group_id)
	);

Line: 517

  SELECT
    NVL(SUM(Primary_Quantity), 0)
  INTO
    x_purchase_qty
  FROM
    MTL_MATERIAL_TRANSACTIONS
  WHERE Acct_Period_ID           = p_acct_period_id
    AND Organization_ID          = p_org_id
    AND Inventory_Item_ID        = p_inventory_item_id
    AND Primary_Quantity         > 0
    AND Cost_Group_ID            = p_cost_group_id
    AND Transfer_Organization_ID IS NULL
    AND nvl(owning_tp_type,2) <> 1
    AND transaction_id NOT IN (SELECT transaction_id
                                 FROM mtl_material_transactions
                                WHERE acct_period_id  = p_acct_period_id
                                  AND organization_id = p_org_id
                                  AND inventory_item_id = p_inventory_item_id
                                  AND transaction_source_type_id = 13
                                  AND transaction_action_id = 24)
    AND transaction_id NOT IN (SELECT transaction_id
                                 FROM mtl_material_transactions
                                WHERE acct_period_id  = p_acct_period_id
                                  AND organization_id = p_org_id
                                  AND inventory_item_id = p_inventory_item_id
                                  AND transaction_source_type_id = 13
                                  AND transaction_action_id = 5);

Line: 588

  SELECT
    NVL(SUM(Primary_Quantity), 0)
  INTO
    x_issue_qty
  FROM
    MTL_MATERIAL_TRANSACTIONS
  WHERE Acct_Period_ID        = p_acct_period_id
    AND Organization_ID       = p_org_id
    AND Inventory_Item_ID     = p_inventory_item_id
    AND Primary_Quantity      < 0
    AND Cost_Group_ID         = p_cost_group_id
    AND Transfer_Organization_ID IS NULL
    AND nvl(owning_tp_type,2) <> 1
    AND transaction_id NOT IN (SELECT transaction_id
                                 FROM mtl_material_transactions
                                WHERE acct_period_id  = p_acct_period_id
                                  AND organization_id = p_org_id
                                  AND inventory_item_id = p_inventory_item_id
                                  AND transaction_source_type_id = 13
                                  AND transaction_action_id = 5);

Line: 650

  l_transfer_rec.last_update_date         := SYSDATE;

Line: 651

  l_transfer_rec.last_updated_by          :=
    NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0);

Line: 656

  l_transfer_rec.last_update_login        :=
    TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'));

Line: 664

  l_transfer_rec.program_update_date      := SYSDATE;

Line: 709

  SELECT
    COUNT(DISTINCT(Acct_Period_ID))
  INTO
    l_status
  FROM
    CST_MGD_INFL_ADJUSTED_COSTS
  WHERE Country_Code    = p_country_code
    AND Organization_ID = p_org_id;

Line: 790

    SELECT
      COUNT(1)
    INTO
      l_record_count
    FROM
      MTL_ITEM_CATEGORIES
    WHERE Inventory_Item_ID = p_inventory_item_id
      AND Organization_ID   = p_org_id
      AND Category_Set_ID   = p_category_set_id;

Line: 804

    SELECT
      COUNT(1)
    INTO
      l_record_count
    FROM
      MTL_ITEM_CATEGORIES
    WHERE Inventory_Item_ID = p_inventory_item_id
      AND Organization_ID   = p_org_id
      AND Category_ID       = p_category_id;

Line: 818

    SELECT
      COUNT(1)
    INTO
      l_record_count
    FROM
      MTL_ITEM_CATEGORIES
    WHERE Inventory_Item_ID = p_inventory_item_id
      AND Organization_ID   = p_org_id
      AND Category_Set_ID   = p_category_set_id
      AND Category_ID       = p_category_id;

Line: 921

  SELECT
    MTL.Transaction_ID
  , MTL.Transfer_Organization_ID
  , NVL(Primary_Quantity, 0) Transfer_In_Qty
  , ORG.Acct_Period_ID TNSF_Acct_Period_ID
  FROM
    MTL_MATERIAL_TRANSACTIONS MTL
  , ORG_ACCT_PERIODS         ORG
  WHERE MTL.Organization_ID   = p_inflation_adjustment_rec.organization_id
    AND MTL.Inventory_Item_ID = p_inflation_adjustment_rec.inventory_item_id
    AND MTL.Acct_Period_ID    = p_inflation_adjustment_rec.acct_period_id
    AND MTL.Primary_Quantity  > 0
    AND MTL.Cost_Group_ID     = p_cost_group_id
    AND MTL.Transfer_Organization_ID <> MTL.Organization_ID
    AND MTL.Transfer_Organization_ID IS NOT NULL
    AND ORG.Organization_ID   = MTL.Transfer_Organization_ID
    AND MTL.transaction_date BETWEEN
        TRUNC(ORG.period_start_date)
    AND (TRUNC(ORG.schedule_close_date) + (86399/86400))
    AND ORG.period_close_date IS NOT NULL
    AND ORG.open_flag <> 'Y'
    AND NVL(MTL.owning_tp_type,2) <> 1
  ORDER BY
    trunc(MTL.transaction_date)
  , MTL.creation_date
  , MTL.transaction_id;

Line: 951

  SELECT
    Transaction_ID
  , Transfer_Organization_ID
  , NVL(Primary_Quantity, 0) Transfer_Out_Qty
  FROM
    MTL_MATERIAL_TRANSACTIONS
  WHERE Organization_ID   = p_inflation_adjustment_rec.organization_id
    AND Inventory_Item_ID = p_inflation_adjustment_rec.inventory_item_id
    AND Acct_Period_ID    = p_inflation_adjustment_rec.acct_period_id
    AND Primary_Quantity  < 0
    AND Cost_Group_ID     = p_cost_group_id
    AND Transfer_Organization_ID <> Organization_ID
    AND Transfer_Organization_ID IS NOT NULL
    AND NVL(owning_tp_type,2) <> 1
  ORDER BY
    trunc(transaction_date)
  , creation_date
  , transaction_id;

Line: 973

SELECT
  default_cost_group_id
FROM MTL_PARAMETERS
WHERE organization_id = c_transfer_in_org_id;

Line: 1477

    SELECT
      Organization_Code
    INTO
      l_transfer_org_code
    FROM
      MTL_PARAMETERS
    WHERE
      Organization_ID = l_err_transfer_org_id;

Line: 1513

PROCEDURE Insert_Inflation_Adj
( p_inflation_adjustment_rec IN
    CST_MGD_INFL_ADJUSTMENT_PUB.Inflation_Adjustment_Rec_Type
)
IS
BEGIN

  INSERT INTO
    CST_MGD_INFL_ADJUSTED_COSTS(
      Inventory_Item_ID
    , Organization_ID
    , Acct_Period_ID
    , Last_Update_Date
    , Last_Updated_By
    , Creation_Date
    , Created_By
    , Last_Update_Login
    , Request_ID
    , Program_Application_ID
    , Program_ID
    , Program_Update_Date
    , Country_Code
    , Begin_Qty
    , Begin_Cost
    , Begin_Inflation_Adj
    , Purchase_Qty
    , Purchase_Cost
    , Actual_Qty
    , Actual_Cost
    , Actual_Inflation_Adj
    , Issue_Qty
    , Issue_Cost
    , Issue_Inflation_Adj
    , Inventory_Adj_Acct_CR
    , Inventory_Adj_Acct_DR
    , Monetary_Corr_Acct_CR
    , Sales_Cost_Acct_DR
    , Historical_Flag
    )
  VALUES(
      p_inflation_adjustment_rec.inventory_item_id
    , p_inflation_adjustment_rec.organization_id
    , p_inflation_adjustment_rec.acct_period_id
    , p_inflation_adjustment_rec.last_update_date
    , p_inflation_adjustment_rec.last_updated_by
    , p_inflation_adjustment_rec.creation_date
    , p_inflation_adjustment_rec.created_by
    , p_inflation_adjustment_rec.last_update_login
    , p_inflation_adjustment_rec.request_id
    , p_inflation_adjustment_rec.program_application_id
    , p_inflation_adjustment_rec.program_id
    , p_inflation_adjustment_rec.program_update_date
    , p_inflation_adjustment_rec.country_code
    , p_inflation_adjustment_rec.begin_qty
    , p_inflation_adjustment_rec.begin_cost
    , p_inflation_adjustment_rec.begin_inflation_adj
    , p_inflation_adjustment_rec.purchase_qty
    , p_inflation_adjustment_rec.purchase_cost
    , p_inflation_adjustment_rec.actual_qty
    , p_inflation_adjustment_rec.actual_cost
    , p_inflation_adjustment_rec.actual_inflation_adj
    , p_inflation_adjustment_rec.issue_qty
    , p_inflation_adjustment_rec.issue_cost
    , p_inflation_adjustment_rec.issue_inflation_adj
    , p_inflation_adjustment_rec.inventory_adj_acct_cr
    , p_inflation_adjustment_rec.inventory_adj_acct_dr
    , p_inflation_adjustment_rec.monetary_corr_acct_cr
    , p_inflation_adjustment_rec.sales_cost_acct_dr
    , p_inflation_adjustment_rec.historical_flag
    );

Line: 1590

                             , 'Insert_Inflation_Adj'
                             );

Line: 1595

END Insert_Inflation_Adj;

Line: 1605

PROCEDURE Insert_Transfer_Entries
( p_tnsf_acct_entry_rec IN  Transfer_Rec_Type
)
IS
BEGIN

  INSERT INTO
    CST_MGD_INFL_TSF_ORG_ENTRIES(
      Transaction_ID
    , Inventory_Item_ID
    , Organization_ID
    , Acct_Period_ID
    , Last_Update_Date
    , Last_Updated_By
    , Creation_Date
    , Created_By
    , Last_Update_Login
    , Request_ID
    , Program_Application_ID
    , Program_ID
    , Program_Update_Date
    , Country_Code
    , Transfer_Organization_ID
    , Entered_DR
    , Entered_CR
    )
  VALUES(
      p_tnsf_acct_entry_rec.transaction_id
    , p_tnsf_acct_entry_rec.inventory_item_id
    , p_tnsf_acct_entry_rec.organization_id
    , p_tnsf_acct_entry_rec.acct_period_id
    , p_tnsf_acct_entry_rec.last_update_date
    , p_tnsf_acct_entry_rec.last_updated_by
    , p_tnsf_acct_entry_rec.creation_date
    , p_tnsf_acct_entry_rec.created_by
    , p_tnsf_acct_entry_rec.last_update_login
    , p_tnsf_acct_entry_rec.request_id
    , p_tnsf_acct_entry_rec.program_application_id
    , p_tnsf_acct_entry_rec.program_id
    , p_tnsf_acct_entry_rec.program_update_date
    , p_tnsf_acct_entry_rec.country_code
    , p_tnsf_acct_entry_rec.transfer_organization_id
    , p_tnsf_acct_entry_rec.entered_dr
    , p_tnsf_acct_entry_rec.entered_cr
    );

Line: 1657

                             , 'Insert_Transfer_Entries'
                             );

Line: 1662

END Insert_Transfer_Entries;

Line: 1770

  Insert_Inflation_Adj
  ( p_inflation_adjustment_rec => l_infl_adjustment_out_rec
  );

Line: 1783

        Insert_Transfer_Entries
        ( p_tnsf_acct_entry_rec => l_tnsf_out_entry_tbl_rec(l_index)
        );

Line: 1801

        Insert_Transfer_Entries
        ( p_tnsf_acct_entry_rec => l_tnsf_in_entry_tbl_rec(l_index)
        );

Line: 1881

  SELECT
    Global_Attribute3
  , Global_Attribute4
  , Global_Attribute5
  INTO
    l_inv_adj_ccid
  , l_monetary_corr_ccid
  , l_sales_cost_ccid
  FROM
    MTL_SYSTEM_ITEMS
  WHERE Organization_ID                        = p_org_id
    AND Inventory_Item_ID                      = p_inv_item_id
    AND SUBSTR(GLOBAL_ATTRIBUTE_CATEGORY, 4,2) = p_country_code;

Line: 1920

    SELECT
      Concatenated_Segments
    INTO
      l_err_item_code
    FROM
      MTL_SYSTEM_ITEMS_KFV
    WHERE Organization_ID   = p_org_id
      AND Inventory_Item_ID = p_inv_item_id;

Line: 1966

  SELECT
    Set_Of_Books_ID
  INTO
    x_set_of_books_id
  FROM
    gl_sets_of_books
  , hr_organization_information
  WHERE set_of_books_id   =  org_information1
    AND upper(org_information_context) = upper('Accounting Information')
    AND organization_id   = p_org_id;

Line: 2005

  SELECT
    Currency_Code
  INTO
    x_currency_code
  FROM
    GL_SETS_OF_BOOKS
  WHERE Set_Of_Books_ID = p_set_of_books_id;

Line: 2084

  SELECT
    Transfer_Organization_ID
  , NVL(SUM(Entered_CR), 0) Entered_CR
  , NVL(SUM(Entered_DR), 0) Entered_DR
  FROM
    CST_MGD_INFL_TSF_ORG_ENTRIES
  WHERE Acct_Period_ID    = p_acct_period_id
    AND Organization_ID   = p_org_id
    AND Inventory_Item_ID = p_inv_item_id
    AND Country_Code      = p_country_code
  GROUP BY Transfer_Organization_ID;

Line: 2099

SELECT
  nvl(precision,0)
FROM fnd_currencies
WHERE currency_code = c_currency_code;

Line: 2476

  INSERT INTO
    GL_INTERFACE(
      Status
    , Set_Of_Books_ID
    , User_JE_Source_Name
    , User_JE_Category_Name
    , Accounting_Date
    , Currency_Code
    , Date_Created
    , Created_By
    , Actual_Flag
    , Entered_DR
    , Entered_CR
    , Code_Combination_ID
    )
  VALUES(
      p_infl_adj_acct_rec.Status
    , p_infl_adj_acct_rec.Set_Of_Books_ID
    , p_infl_adj_acct_rec.User_JE_Source_Name
    , p_infl_adj_acct_rec.User_JE_Category_Name
    , p_infl_adj_acct_rec.Accounting_Date
    , p_infl_adj_acct_rec.Currency_Code
    , p_infl_adj_acct_rec.Date_Created
    , p_infl_adj_acct_rec.Created_By
    , p_infl_adj_acct_rec.Actual_Flag
    , p_infl_adj_acct_rec.Entered_DR
    , p_infl_adj_acct_rec.Entered_CR
    , p_infl_adj_acct_rec.Code_Combination_ID
    );

Line: 2550

  INSERT INTO
    CST_MGD_INFL_ADJ_PER_STATUSES(
      Organization_ID
    , Acct_Period_ID
    , Last_Update_Date
    , Last_Updated_By
    , Creation_Date
    , Created_By
    , Last_Update_Login
    , Request_ID
    , Program_Application_ID
    , Program_ID
    , Program_Update_Date
    , STATUS
    )
  VALUES(
      p_org_id
    , p_acct_period_id
    , SYSDATE
    , NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0)
    , SYSDATE
    , NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0)
    , TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'))
    , TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID'))
    , TO_NUMBER(FND_PROFILE.Value('PROG_APPL_ID'))
    , TO_NUMBER(FND_PROFILE.Value('CONC_PROG_ID'))
    , SYSDATE
    , 'PROCESS'
    );

Line: 2613

PROCEDURE Update_Infl_Period_Status
( p_org_id         IN         NUMBER
, p_acct_period_id IN         NUMBER
, x_return_status  OUT NOCOPY VARCHAR2
)
IS
BEGIN

  x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 2623

  UPDATE CST_MGD_INFL_ADJ_PER_STATUSES
    SET Status                 = 'FINAL'
      , Last_Update_Date       = SYSDATE
      , Last_Updated_By        = NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0)
      , Creation_Date          = SYSDATE
      , Created_By             = NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0)
      , Last_Update_Login      = TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'))
      , Request_ID             = TO_NUMBER(FND_PROFILE.Value('CONC_REQUEST_ID'))
      , Program_Application_ID = TO_NUMBER(FND_PROFILE.Value('PROG_APPLD_ID'))
      , Program_ID             = TO_NUMBER(FND_PROFILE.Value('CONC_PROG_ID'))
      , Program_Update_Date    = SYSDATE
  WHERE organization_id = p_org_id
    AND acct_period_id  = p_acct_period_id;

Line: 2649

END Update_Infl_Period_Status;

Line: 2746

  IF (l_historical_infl_adj_rec.last_update_date IS NULL)
  THEN
    l_historical_infl_adj_rec.last_update_date := SYSDATE;

Line: 2751

  IF (l_historical_infl_adj_rec.last_updated_by IS NULL)
  THEN
    l_historical_infl_adj_rec.last_updated_by :=
      NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),0);

Line: 2768

  IF (l_historical_infl_adj_rec.last_update_login IS NULL)
  THEN
    l_historical_infl_adj_rec.last_update_login :=
      TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'));

Line: 2792

  IF (l_historical_infl_adj_rec.program_update_date IS NULL)
  THEN
    l_historical_infl_adj_rec.program_update_date := SYSDATE;

Line: 2859

  SELECT
    SUM(Period_End_Unit_Cost)
  , DECODE(SUM(NVL(ABS(Period_End_Quantity), 1) * Period_End_Unit_Cost), 0, SUM(Period_End_Unit_Cost)/COUNT(*), SUM(NVL(ABS(Period_End_Quantity), 1) * Period_End_Unit_Cost)) /
  DECODE(SUM(NVL(ABS(Period_End_Quantity), 1)), 0, 1, SUM(NVL(ABS(Period_End_Quantity), 1)))
   FROM (
	SELECT  rollback_quantity period_end_quantity,
		decode(rollback_quantity,0,0,rollback_value/rollback_quantity) period_end_unit_cost
	  FROM    cst_period_close_summary
	  WHERE Organization_ID   = c_org_id
    	   AND Acct_Period_ID    = c_acct_period_id
    	   AND Inventory_Item_ID = c_inv_item_id
    	   AND Cost_Group_ID     = NVL(c_cost_group_id,Cost_Group_ID)
	UNION ALL
	SELECT  period_end_quantity, period_end_unit_cost
	  FROM    mtl_per_close_dtls
	  WHERE Organization_ID   = c_org_id
    	  AND Acct_Period_ID    = c_acct_period_id
    	  AND Inventory_Item_ID = c_inv_item_id
    	  AND Cost_Group_ID     = NVL(c_cost_group_id,Cost_Group_ID)
	);