APPS.OE_OPT_PROCESS SQL Statements

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

Line: 39

SELECT   'Y'
        ,DECODE( BOMEXP.COMPONENT_CODE,
		 x_options_comp_code, NULL, OELIN.LINE_ID )
        ,BOMEXP.COMPONENT_CODE
  FROM   BOM_EXPLOSIONS  BOMEXP
        ,MTL_SYSTEM_ITEMS MTLITM
        ,SO_LINES OELIN
  WHERE  BOMEXP.TOP_BILL_SEQUENCE_ID = x_lines_comp_seq_id
  AND    BOMEXP.EXPLOSION_TYPE = 'OPTIONAL'
  AND    BOMEXP.PLAN_LEVEL <= x_options_plan_level
  AND    BOMEXP.EFFECTIVITY_DATE <=
         NVL(x_lines_creation_date,  SYSDATE)
  AND    BOMEXP.DISABLE_DATE >
         NVL(x_lines_creation_date,  SYSDATE)
  AND    BOMEXP.COMPONENT_CODE =
         SUBSTR( x_options_comp_code, 1,
		 LENGTH( BOMEXP.COMPONENT_CODE ) )
  AND    LENGTH( BOMEXP.COMPONENT_CODE ) <=
         LENGTH( x_options_comp_code )
  AND    MTLITM.ORGANIZATION_ID = BOMEXP.ORGANIZATION_ID
  AND    MTLITM.INVENTORY_ITEM_ID = BOMEXP.COMPONENT_ITEM_ID
  AND    DECODE( MTLITM.BOM_ITEM_TYPE,
                 1, NVL( MTLITM.REPLENISH_TO_ORDER_FLAG, 'N' ),
                 4, DECODE( MTLITM.REPLENISH_TO_ORDER_FLAG,
                            'Y', DECODE( MTLITM.BUILD_IN_WIP_FLAG,
                                         'Y', 'Y', 'N' ),
                            'N' ),
                 'N' ) = 'Y'
  AND    OELIN.PARENT_LINE_ID (+) = CONFIGURATION_PARENT_LINE_ID
  AND    OELIN.SERVICE_PARENT_LINE_ID (+) is NULL
  AND    OELIN.COMPONENT_CODE (+) = BOMEXP.COMPONENT_CODE
  ORDER BY BOMEXP.SORT_ORDER;

Line: 88

     SELECT DECODE( BOM_ITEM_TYPE,
               1, 'MODEL',
               2, 'CLASS',
               4, DECODE( PICK_COMPONENTS_FLAG, 'Y', 'KIT', 'STANDARD' ),
               'UNKNOWN' ),
      NVL(SERVICEABLE_PRODUCT_FLAG,'N')
      INTO   Options_ITEM_TYPE_CODE,
          Serviceable_Flag
      FROM   MTL_SYSTEM_ITEMS
      WHERE  ORGANIZATION_ID = P_Organization_Id
      AND    INVENTORY_ITEM_ID = Options_INVENTORY_ITEM_ID;

Line: 100

     select meaning into Options_Item_Type from so_lookups
       where lookup_type = 'ITEM TYPE' and
             lookup_code = Options_Item_Type_Code;

Line: 189

SELECT NVL(SERVICEABLE_PRODUCT_FLAG,'N')
INTO   Options_Serviceable_Flag
FROM   MTL_SYSTEM_ITEMS
WHERE  ORGANIZATION_ID = World_Organization_Id
AND    INVENTORY_ITEM_ID = Options_Inventory_Item_Id;

Line: 222

        SELECT COMPONENT_CODE
        INTO   Options_ATO_Parent_Comp_Code
        FROM   SO_LINES
        WHERE  LINE_ID = Options_ATO_Line_Id;

Line: 242

PROCEDURE Insert_Installn_Details
(
        P_Line_Id                           IN NUMBER,
        P_User_Id                           IN NUMBER,
        P_Login_Id                          IN NUMBER,
        P_Configuration_Parent_Line_Id      IN NUMBER,
	P_Return_Status			    OUT VARCHAR2
)
is

begin

P_Return_Status:=OE_SUCCESS;

Line: 256

INSERT INTO SO_LINE_SERVICE_DETAILS
(      LINE_SERVICE_DETAIL_ID
     , CREATION_DATE
     , CREATED_BY
     , LAST_UPDATE_DATE
     , LAST_UPDATED_BY
     , LAST_UPDATE_LOGIN
     , LINE_ID
     , SOURCE_LINE_SERVICE_DETAIL_ID
     , TRANSACTION_TYPE_ID
     , SYSTEM_ID
     , SYSTEM_TYPE_CODE
     , CUSTOMER_PRODUCT_ID
     , CUSTOMER_PRODUCT_TYPE_CODE
     , CUSTOMER_PRODUCT_QUANTITY
     , INSTALLATION_SITE_USE_ID
     , TECHNICAL_CONTACT_ID
     , SERVICE_ADMIN_CONTACT_ID
     , CONTEXT
     , ATTRIBUTE1
     , ATTRIBUTE2
     , ATTRIBUTE3
     , ATTRIBUTE4
     , ATTRIBUTE5
     , ATTRIBUTE6
     , ATTRIBUTE7
     , ATTRIBUTE8
     , ATTRIBUTE9
     , ATTRIBUTE10
     , ATTRIBUTE11
     , ATTRIBUTE12
     , ATTRIBUTE13
     , ATTRIBUTE14
     , ATTRIBUTE15
)
SELECT SO_LINE_SERVICE_DETAILS_S.NEXTVAL
     , SYSDATE
     , P_User_Id
     , SYSDATE
     , P_User_Id
     , P_Login_Id
     , P_Line_Id
     , LINE_SERVICE_DETAIL_ID
     , TRANSACTION_TYPE_ID
     , SYSTEM_ID
     , SYSTEM_TYPE_CODE
     , CUSTOMER_PRODUCT_ID
     , CUSTOMER_PRODUCT_TYPE_CODE
     , CUSTOMER_PRODUCT_QUANTITY
     , INSTALLATION_SITE_USE_ID
     , TECHNICAL_CONTACT_ID
     , SERVICE_ADMIN_CONTACT_ID
     , CONTEXT
     , ATTRIBUTE1
     , ATTRIBUTE2
     , ATTRIBUTE3
     , ATTRIBUTE4
     , ATTRIBUTE5
     , ATTRIBUTE6
     , ATTRIBUTE7
     , ATTRIBUTE8
     , ATTRIBUTE9
     , ATTRIBUTE10
     , ATTRIBUTE11
     , ATTRIBUTE12
     , ATTRIBUTE13
     , ATTRIBUTE14
     , ATTRIBUTE15
FROM   SO_LINE_SERVICE_DETAILS
WHERE  LINE_ID = P_Configuration_Parent_Line_Id
AND    TRANSACTION_TYPE_ID = 2;

Line: 333

                                'OE_OPT_PROCESS.Insert_Service_Details',
                                Operation=>'',
				Object=>'OPTION',
                                Message=>' When Others');

Line: 338

end Insert_Installn_Details;

Line: 340

/* Select statement modified to get low,high quantities from
   the BOM - 895383 (894316 in Rel 11) */

PROCEDURE Query_BOM_Quantity
(
        P_Creation_Date_Time                IN DATE,
        P_Component_Sequence_Id             IN NUMBER,
        P_Component_Code                    IN VARCHAR2,
        P_Model_Open_Quantity               IN NUMBER,
        P_Component_Quantity                IN OUT NUMBER,
        P_Low_Quantity                      IN OUT NUMBER,
        P_High_Quantity                     IN OUT NUMBER,
        P_Return_Status                     OUT VARCHAR2
)
is

begin

	P_Return_Status:=OE_SUCCESS;

Line: 360

        SELECT EXTENDED_QUANTITY / COMPONENT_QUANTITY *
               P_Model_Open_Quantity
        ,      NVL( LOW_QUANTITY, 1 )
        ,      HIGH_QUANTITY
        INTO   P_Component_Quantity
        ,      P_Low_quantity
        ,      P_High_Quantity
        FROM   BOM_EXPLOSIONS
        WHERE  TOP_BILL_SEQUENCE_ID = P_Component_Sequence_Id
        AND    COMPONENT_CODE = P_Component_Code
	   AND    EXPLOSION_TYPE = 'OPTIONAL'
	   AND    PLAN_LEVEL > 0
	   AND    EFFECTIVITY_DATE <=
          NVL(P_Creation_Date_Time,
          SYSDATE)
	   AND    DISABLE_DATE >
          NVL(P_Creation_Date_Time,
          SYSDATE);

Line: 393

PROCEDURE Set_Update_Subconfig_Flag
(
        P_Row_Id                            IN VARCHAR2,
        P_Ordered_Quantity                  IN NUMBER,
        P_Update_Subconfig_Flag             OUT VARCHAR2,
        P_Return_Status                     OUT VARCHAR2
)
is

L_Dummy NUMBER;

Line: 406

     SELECT NULL INTO L_Dummy
	FROM   SO_LINES
	WHERE  ROWID = P_Row_Id
	AND    NVL( ORDERED_QUANTITY, -1 )
	<> NVL( P_Ordered_Quantity, -1 );

Line: 412

      P_Update_Subconfig_Flag:='Y';

Line: 418

      P_Update_Subconfig_Flag:='N';

Line: 424

                                'OE_OPT_PROCESS.Set_Update_Subconfig_Flag',
                                Operation=>'',
                                Object=>'OPTION',
                                Message=>' When Others');

Line: 429

end Set_Update_Subconfig_Flag;