APPS.CTO_GOP_INTERFACE_PK SQL Statements

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

Line: 25

|                          insertion into BCOL
|
|             01-22-2004    Kiran Konada
|                          bugfix 3391383
|                          config_orgs attribute from model is inserted into bcol_gt
|                          bug was we tried to get for config_item_id
|
|             02-23-2004   Kiran Konada
|                          bugfix 3259017
|
|             05-17-2004   Kiran Konada
|                          bugfix 3555026
|                          --null value in config_orgs should be treated as
|                            based on sourcing
|                          --When ATP passes null in ship_from_org_id, we should
|                            NOT default to any other organization
|                            AS that org could be a ware house on SO pad
|                            during intial scheduling and hence bcol could have
|                            the data AND would create a problem in re-use,
|                            as configitem is reused if ware house is same
|                            before and after re-scheduling
-------------------------------------------------------------------------------
*/
G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_GOP_INTERFACE_PK';

Line: 82

  SELECT config_item_id,
         line_id,
         link_to_line_id,
	 parent_ato_line_id,
	 gop_parent_ato_line_id,
	 ato_line_id,
	 top_model_line_id,
	 inventory_item_id,
	 ordered_quantity,
	 qty_per_parent_model,
	 ship_from_org_id,
	 validation_org,
	 plan_level,
	 wip_supply_type,
	 bom_item_type,
	 reuse_config,
	 perform_match,
	 config_creation,
	 option_specific,
	 oss_error_code
  FROM bom_cto_order_lines_gt;

Line: 113

     DELETE FROM bom_cto_order_lines_gt;

Line: 135

     select count(*)
     into   l_count
     from   bom_cto_order_lines_gt;

Line: 139

     oe_debug_pub.add('count before insert into bCOL =>'||l_count,1);

Line: 164

         INSERT INTO bom_cto_order_lines_gt
	      (
	      ATO_LINE_ID,
	      COMPONENT_CODE,
	      COMPONENT_SEQUENCE_ID,
	      BOM_ITEM_TYPE,
	      WIP_SUPPLY_TYPE,
	      INVENTORY_ITEM_ID,
	      LINE_ID,
	      LINK_TO_LINE_ID,
	      ORDERED_QUANTITY,
	      ORDER_QUANTITY_UOM,
	      TOP_MODEL_LINE_ID,
	      SHIP_FROM_ORG_ID,
	      config_item_id,
	      VALIDATION_ORG --3503764
	      )
	 VALUES
	      (
	       p_match_rec_of_tab.ato_line_id(i),
	       p_match_rec_of_tab.component_code(i),
	       p_match_rec_of_tab.component_sequence_id(i),
			--for oss pefromance improvement
			--added -1
			--bom_item_type
	       decode(p_match_rec_of_tab.top_model_line_id(i),null,-1, --ato item
	              -- 1 for topst ato model and default is -1
	              decode(p_match_rec_of_tab.ato_line_id(i),p_match_rec_of_tab.line_id(i),1,-1)
		      ),
	       -1,    --wip_supply_type
	       p_match_rec_of_tab.inventory_item_id(i),
	       p_match_rec_of_tab.line_id(i),
	       p_match_rec_of_tab.link_to_line_id(i),
	       p_match_rec_of_tab.ordered_quantity(i),
	       p_match_rec_of_tab.order_quantity_uom(i),
	       p_match_rec_of_tab.top_model_line_id(i),
	       nvl(p_match_rec_of_tab.ship_from_org_id(i),-99),--3555026
	       --need for better prformance of OSS code
	       --if independent ato line order
	       --populate inv_item_id as config item id
	       --conatct info : kiran/renga
	       decode(p_match_rec_of_tab.top_model_line_id(i),
		            null,
		            p_match_rec_of_tab.inventory_item_id(i)
	              ),

	       p_match_rec_of_tab.validation_org(i)--3503764
	      );

Line: 214

	       oe_debug_pub.add(sql%rowcount||' rows inserted into bcol_gt',5);

Line: 220

	SELECT 'Y'
	INTO   l_model_exists
	FROM bom_cto_order_lines_gt
	WHERE line_id = ato_line_id
	AND   top_model_line_id is not null
	AND rownum = 1;

Line: 256

	    UPDATE bom_cto_order_lines_gt child
	    SET    qty_per_parent_model =
	                  --used round to be consistent with can_configuration code
	 	 ( SELECT  ROUND(child.ordered_quantity/parent.ordered_quantity,7)
		   FROM   bom_cto_order_lines_gt parent
		   WHERE  child.parent_ato_line_id= parent.line_id
		  )
	    --to filter out ato item order lines
	    WHERE top_model_line_id is not null;

Line: 335

           UPDATE /*+ INDEX (GT BOM_CTO_ORDER_LINES_GT_N5) */ bom_cto_order_lines_gt GT
	   SET GT.config_creation = ( SELECT nvl(MTL.config_orgs,1)--3555026
	                              FROM   mtl_system_items MTL
				      WHERE  MTL.inventory_item_id = GT.inventory_item_id -- bugfix 3391383
				      AND    MTL.organization_id = GT.validation_org--3555026
				      AND    GT.bom_item_type = '1'
				      AND    GT.config_item_id is not null
                                    )
	   WHERE GT.bom_item_type = '1'
	   AND   GT.config_item_id is not null;

Line: 347

	     oe_debug_pub.add('Updated  '||sql%rowcount||'  model rows with config_orgs or config_creation attribute',5);

Line: 378

     SELECT  oss_error_code,
	     config_item_id,
	     parent_ato_line_id,
	     gop_parent_ato_line_id,
	     bom_item_type,
	     wip_supply_type
     BULK COLLECT INTO
	     p_match_rec_of_tab.oss_error_code,
			--during Ut make sure next statement
			--over writes existing values , ifnot
			--additional rows may get created during
			--for MATCH 0n cases. remove comment after UT
	     p_match_rec_of_tab.config_item_id,
             p_match_rec_of_tab.parent_ato_line_id,
	     p_match_rec_of_tab.gop_parent_ato_line_id,
	     p_match_rec_of_tab.bom_item_type,
	     p_match_rec_of_tab.wip_supply_type
     FROM bom_cto_order_lines_gt
     ORDER BY line_id;  --Bugfix 6055375

Line: 463

    DELETE FROM bom_cto_order_lines_gt;