APPS.INVPULI4 SQL Statements

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

Line: 36

   /*NP 06SEP94 Comment: Cursor cc selects the attr_name and value
   **for those attributes which are under default or under stat control ie. status_control_code = 1 or 2
   **These are the ones which will be affected by status.NP 06MAY96 Added xset_id logic
   */

   -- CURSOR cc is
   CURSOR cc(p_status_code VARCHAR2) is    -- bug 7137702
        select v.attribute_name,
               v.attribute_value,
               a.status_control_code,
               a.control_level
        from mtl_item_status s,
             mtl_status_attribute_values v,
             mtl_item_attributes a
        --      mtl_system_items_interface m
        -- where s.inventory_item_status_code = m.inventory_item_status_code
        -- and   m.rowid = p_rowid
        where s.inventory_item_status_code = p_status_code    -- bug 7137702
        and   s.inventory_item_status_code = v.inventory_item_status_code
        and   DECODE(s.disable_date,NULL,SYSDATE + 1,s.disable_date) > SYSDATE
        and   v.attribute_name = a.attribute_name
        and   a.STATUS_CONTROL_CODE in ( 1,2,3 )
        order by v.attribute_name desc;

Line: 62

   select B.STOCK_ENABLED_FLAG,
          B.PURCHASING_ENABLED_FLAG,
          B.CUSTOMER_ORDER_ENABLED_FLAG,
          B.INTERNAL_ORDER_ENABLED_FLAG,
          B.MTL_TRANSACTIONS_ENABLED_FLAG,
          B.BOM_ENABLED_FLAG,
          B.BUILD_IN_WIP_FLAG,
          B.INVOICE_ENABLED_FLAG,
          B.RECIPE_ENABLED_FLAG,
          B.PROCESS_EXECUTION_ENABLED_FLAG
    FROM  MTL_SYSTEM_ITEMS B
         ,MTL_PARAMETERS PARAM
    WHERE B.INVENTORY_ITEM_ID   = item_id
    AND   B.ORGANIZATION_ID     = PARAM.MASTER_ORGANIZATION_ID
    AND   PARAM.ORGANIZATION_ID = org_id;

Line: 80

     select B.STOCK_ENABLED_FLAG,B.PURCHASING_ENABLED_FLAG,
	    B.CUSTOMER_ORDER_ENABLED_FLAG,B.INTERNAL_ORDER_ENABLED_FLAG,
	    B.MTL_TRANSACTIONS_ENABLED_FLAG,B.BOM_ENABLED_FLAG,
	    B.BUILD_IN_WIP_FLAG,B.INVOICE_ENABLED_FLAG,
	    B.RECIPE_ENABLED_FLAG,B.PROCESS_EXECUTION_ENABLED_FLAG
      from  MTL_SYSTEM_ITEMS B
      where INVENTORY_ITEM_ID = item_id
      and   ORGANIZATION_ID =   org_id;

Line: 111

   SELECT * INTO AttRec
   FROM MTL_SYSTEM_ITEMS_INTERFACE
   WHERE ROWID = p_rowid ;

Line: 118

      SELECT 'Y' INTO Master_Org
      FROM MTL_PARAMETERS
      WHERE ORGANIZATION_ID = master_organization_id
      AND ORGANIZATION_ID   = org_id;

Line: 127

   IF ((AttRec.INVENTORY_ITEM_STATUS_CODE is not null AND AttRec.TRANSACTION_TYPE = 'UPDATE' )
       OR ( AttRec.TRANSACTION_TYPE = 'CREATE' ))
   THEN

      --Start : 6531918 :  Fetching outside the attrs loop
      IF Master_Org ='N' THEN
         OPEN master_cur;

Line: 138

      IF AttRec.TRANSACTION_TYPE = 'UPDATE' THEN
         OPEN  org_cur;

Line: 192

		  ELSIF (AttRec.TRANSACTION_TYPE = 'UPDATE') THEN
                      AttRec.STOCK_ENABLED_FLAG := NVL(AttRec.STOCK_ENABLED_FLAG, org_rec.STOCK_ENABLED_FLAG );

Line: 214

		  ELSIF (AttRec.TRANSACTION_TYPE = 'UPDATE') THEN
                     AttRec.MTL_TRANSACTIONS_ENABLED_FLAG := NVL(AttRec.MTL_TRANSACTIONS_ENABLED_FLAG,org_rec.MTL_TRANSACTIONS_ENABLED_FLAG);

Line: 236

		  ELSIF (AttRec.TRANSACTION_TYPE = 'UPDATE') THEN
                     AttRec.PURCHASING_ENABLED_FLAG := NVL(AttRec.PURCHASING_ENABLED_FLAG, org_rec.PURCHASING_ENABLED_FLAG );

Line: 258

        	  ELSIF (AttRec.TRANSACTION_TYPE = 'UPDATE') THEN
		     AttRec.INVOICE_ENABLED_FLAG := NVL(AttRec.INVOICE_ENABLED_FLAG,org_rec.INVOICE_ENABLED_FLAG );

Line: 280

                  ELSIF (AttRec.TRANSACTION_TYPE = 'UPDATE') THEN
                     AttRec.BUILD_IN_WIP_FLAG := NVL(AttRec.BUILD_IN_WIP_FLAG, org_rec.BUILD_IN_WIP_FLAG );

Line: 302

		  ELSIF (AttRec.TRANSACTION_TYPE = 'UPDATE') THEN
		     AttRec.CUSTOMER_ORDER_ENABLED_FLAG := NVL(AttRec.CUSTOMER_ORDER_ENABLED_FLAG,org_rec.CUSTOMER_ORDER_ENABLED_FLAG);

Line: 324

		  ELSIF (AttRec.TRANSACTION_TYPE = 'UPDATE') THEN
		     AttRec.INTERNAL_ORDER_ENABLED_FLAG := NVL(AttRec.INTERNAL_ORDER_ENABLED_FLAG,org_rec.INTERNAL_ORDER_ENABLED_FLAG );

Line: 345

	       ELSIF (AttRec.TRANSACTION_TYPE = 'UPDATE') THEN
	          AttRec.BOM_ENABLED_FLAG := NVL(AttRec.BOM_ENABLED_FLAG,org_rec.BOM_ENABLED_FLAG );

Line: 363

	       ELSIF (AttRec.TRANSACTION_TYPE = 'UPDATE') THEN
                  AttRec.RECIPE_ENABLED_FLAG := NVL(AttRec.RECIPE_ENABLED_FLAG,org_rec.RECIPE_ENABLED_FLAG );

Line: 385

	          ELSIF (AttRec.TRANSACTION_TYPE = 'UPDATE') THEN
		     AttRec.PROCESS_EXECUTION_ENABLED_FLAG := NVL(AttRec.PROCESS_EXECUTION_ENABLED_FLAG,org_rec.PROCESS_EXECUTION_ENABLED_FLAG );

Line: 394

      update MTL_SYSTEM_ITEMS_INTERFACE
      set
       STOCK_ENABLED_FLAG = AttRec.STOCK_ENABLED_FLAG,
       MTL_TRANSACTIONS_ENABLED_FLAG = AttRec.MTL_TRANSACTIONS_ENABLED_FLAG,
       PURCHASING_ENABLED_FLAG = AttRec.PURCHASING_ENABLED_FLAG,
       INVOICE_ENABLED_FLAG = AttRec.INVOICE_ENABLED_FLAG,
       BUILD_IN_WIP_FLAG = AttRec.BUILD_IN_WIP_FLAG,
       CUSTOMER_ORDER_ENABLED_FLAG = AttRec.CUSTOMER_ORDER_ENABLED_FLAG,
       INTERNAL_ORDER_ENABLED_FLAG = AttRec.INTERNAL_ORDER_ENABLED_FLAG,
       BOM_ENABLED_FLAG = AttRec.BOM_ENABLED_FLAG,
       RECIPE_ENABLED_FLAG = AttRec.RECIPE_ENABLED_FLAG,
       PROCESS_EXECUTION_ENABLED_FLAG = AttRec.PROCESS_EXECUTION_ENABLED_FLAG
      where rowid = p_rowid ;

Line: 409

   IF (AttRec.INVENTORY_ITEM_STATUS_CODE is null and AttRec.TRANSACTION_TYPE = 'UPDATE') THEN
      update mtl_system_items_interface
      set inventory_item_status_code = (select msi.inventory_item_status_code
                                        from mtl_system_items msi
                                        where msi.inventory_item_id = AttRec.inventory_item_id
                                        and msi.organization_id = AttRec.organization_id)
      where rowid = p_rowid;