APPS.EGO_BOM_BULKLOAD_PVT_PKG SQL Statements

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

Line: 31

     SELECT VALUE
      FROM V$PARAMETER
     WHERE NAME = 'utl_file_dir';

Line: 79

  SELECT Bill_Sequence_Id
    INTO l_id
   FROM Bom_Bill_Of_Materials
  WHERE Assembly_Item_Id = p_assembly_item_id
    AND NVL(Alternate_Bom_Designator, 'NONE') =
             DECODE(p_alternate_bom_designator,NULL,'NONE',p_alternate_bom_designator)
      AND Organization_Id = p_organization_id;

Line: 109

  SELECT  Organization_Id
  INTO    l_id
  FROM    Mtl_Parameters
  WHERE   Organization_Code = p_organization;

Line: 170

  UPDATE EGO_BULKLOAD_INTF EBI
   SET  EBI.PROCESS_STATUS =
    (
      SELECT BMI.PROCESS_FLAG
      FROM   BOM_BILL_OF_MTLS_INTERFACE BMI
      WHERE  BMI.TRANSACTION_ID = EBI.TRANSACTION_ID
    )
  WHERE EXISTS
    (
      SELECT 'X'
      FROM   BOM_BILL_OF_MTLS_INTERFACE BMI
      WHERE  BMI.TRANSACTION_ID = EBI.TRANSACTION_ID
    )
    AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;

Line: 186

  UPDATE EGO_BULKLOAD_INTF EBI
   SET  EBI.PROCESS_STATUS =
    (
      SELECT BICI.PROCESS_FLAG
      FROM   BOM_INVENTORY_COMPS_INTERFACE BICI
      WHERE  BICI.TRANSACTION_ID = EBI.TRANSACTION_ID
    )
  WHERE EXISTS
    (
      SELECT 'X'
      FROM   BOM_INVENTORY_COMPS_INTERFACE BICI
      WHERE  BICI.TRANSACTION_ID = EBI.TRANSACTION_ID
    )
    AND EBI.RESULTFMT_USAGE_ID = p_resultfmt_usage_id;

Line: 251

  l_dyn_sql_select      VARCHAR2(10000);

Line: 252

  l_dyn_sql_insert      VARCHAR2(10000);

Line: 255

  l_cursor_select       INTEGER;

Line: 284

  G_TXN_UPDATE          VARCHAR2(10) := 'UPDATE';

Line: 285

  G_TXN_DELETE          VARCHAR2(10) := 'DELETE';

Line: 323

  G_DEL_GROUP_DESC    VARCHAR2(240) := 'Delete Group for EGO BOM Bulkload Structures';

Line: 340

    SELECT
      erf.Attribute_Code,
      erf.Intf_Column_Name,
      bcc.Bom_Intf_Column_Name ,
      bcc.Bom_Intf_Table_Name,
      bcc.Operation_Type
    FROM
      Ego_Results_Fmt_Usages erf,
      Bom_Component_Columns bcc
    WHERE
      Region_Code = 'BOM_RESULT_DUMMY_REGION'
    AND
      Region_Application_Id = 702
    AND
      Customization_Application_Id = 431
    AND
      Resultfmt_Usage_Id = c_Resultfmt_Usage_Id
    AND
      bcc.Attribute_Code = erf.Attribute_Code
    AND
      (   bcc.Parent_Entity IS NULL
      OR  BCC.Attribute_Code = 'ITEM_NUMBER'
      OR  BCC.Attribute_Code = 'PARENT_NAME')
    AND
      erf.Attribute_Code NOT LIKE '%$$%';

Line: 369

    SELECT
      ASSEMBLY_ITEM_ID,
      ORGANIZATION_ID,
      ASSEMBLY_TYPE,
      PROCESS_FLAG,
      ORGANIZATION_CODE,
      COMMON_ORG_CODE,
      ITEM_NUMBER,
      IMPLEMENTATION_DATE
    FROM
      BOM_BILL_OF_MTLS_INTERFACE
    WHERE
      PROCESS_FLAG = 1
    AND
      REQUEST_ID = C_REQUEST_ID;

Line: 424

    DELETE FROM  EGO_BULKLOAD_INTF
    WHERE RESULTFMT_USAGE_ID = p_resultfmt_usage_id
    AND PROCESS_STATUS <> 1;

Line: 433

    UPDATE EGO_BULKLOAD_INTF
     SET  Transaction_Id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
    WHERE  Resultfmt_Usage_Id = p_Resultfmt_Usage_Id AND PROCESS_STATUS = 1 ;

Line: 470

    l_dyn_sql_insert := '';

Line: 471

    l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (REQUEST_ID, Transaction_Type ';

Line: 472

    l_dyn_sql_insert := l_dyn_sql_insert || ', Transaction_Id, Process_Flag, Item_Number ';

Line: 473

    l_dyn_sql_insert := l_dyn_sql_insert || ', Organization_Code, Alternate_Bom_Designator) ';

Line: 475

    l_dyn_sql_select := '';

Line: 476

    l_dyn_sql_select := l_dyn_sql_select || ' SELECT REQUEST_ID, Transaction_Type, Transaction_Id, 1, ' || l_item_col_name;

Line: 477

    l_dyn_sql_select := l_dyn_sql_select || ', ' || G_INTF_ORG_CODE;

Line: 478

    l_dyn_sql_select := l_dyn_sql_select || ', DECODE(' || G_INTF_STRUCT_NAME || ',Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,' || G_INTF_STRUCT_NAME || ')';

Line: 479

    l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID ';

Line: 480

    l_dyn_sql_select := l_dyn_sql_select || ' AND  PROCESS_STATUS = 1 AND ' || l_parent_column || ' IS NULL ';

Line: 482

    l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;

Line: 493

    l_dyn_sql_insert := '';

Line: 494

    l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( REQUEST_ID, Transaction_Type, Transaction_Id, Process_Flag, ';

Line: 495

    l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, ';

Line: 496

    l_dyn_sql_select := '';

Line: 497

    l_dyn_sql_select := l_dyn_sql_select || 'SELECT REQUEST_ID, TRANSACTION_TYPE, Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';

Line: 498

    l_dyn_sql_select := l_dyn_sql_select || 'DECODE(' || G_INTF_STRUCT_NAME || ',Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,' || G_INTF_STRUCT_NAME || '), ';

Line: 499

    l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ';

Line: 509

          l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_EFFECTIVITY_DATE,';

Line: 512

          l_str := 'DECODE(( SELECT To_Char(Effectivity_Date,''DD-MON-YYYY HH24:MI:SS'') FROM Bom_inventory_Components WHERE Component_Sequence_Id = ' || G_INTF_COMP_SEQ_ID || ')';

Line: 515

          l_dyn_sql_select := l_dyn_sql_select || l_str;

Line: 518

              l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_OPERATION_SEQ_NUM,';

Line: 520

              l_dyn_sql_insert := l_dyn_sql_insert || 'NEW_FROM_END_ITEM_UNIT_NUMBER,';

Line: 522

              l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';

Line: 526

              l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';

Line: 528

              l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';

Line: 535

    l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);

Line: 536

    l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);

Line: 537

    l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';

Line: 538

    l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';

Line: 539

    l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID ';

Line: 540

    l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ' || l_parent_column || ' IS NOT NULL ';

Line: 541

    l_dyn_sql_select := l_dyn_sql_select || ' AND Transaction_Type = ''' || G_TXN_UPDATE || ''' ';

Line: 543

    l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;

Line: 560

    l_dyn_sql_cursor := l_dyn_sql_cursor || ' SELECT Distinct Transaction_Id, ' || l_parent_column ;

Line: 573

    l_cursor_select := Dbms_Sql.Open_Cursor;

Line: 574

    Dbms_Sql.Parse(l_cursor_select, l_dyn_sql_cursor, Dbms_Sql.NATIVE);

Line: 575

    Dbms_Sql.Define_Column(l_cursor_select, 1, L_TRANSACTION_ID);

Line: 576

    Dbms_Sql.Define_Column(l_cursor_select, 2, L_PARENT_NAME, 5000);

Line: 577

    Dbms_Sql.Define_Column(l_cursor_select, 3, L_ORGANIZATION_CODE, 5000);

Line: 578

    Dbms_Sql.Define_Column(l_cursor_select, 4, L_STRUCTURE_NAME, 5000);

Line: 580

    Dbms_Sql.Bind_Variable(l_cursor_select,':RESULTFMT_USAGE_ID', p_resultfmt_usage_id);

Line: 587

    l_cursor_execute := Dbms_Sql.EXECUTE(l_cursor_select);

Line: 594

      IF (Dbms_Sql.Fetch_Rows(l_cursor_select) > 0) THEN
        Dbms_Sql.Column_Value(l_cursor_select,1,L_TRANSACTION_ID);

Line: 596

        Dbms_Sql.Column_Value(l_cursor_select,2,L_PARENT_NAME);

Line: 597

        Dbms_Sql.Column_Value(l_cursor_select,3,L_ORGANIZATION_CODE);

Line: 598

        Dbms_Sql.Column_Value(l_cursor_select,4,L_STRUCTURE_NAME);

Line: 604

        INSERT INTO BOM_BILL_OF_MTLS_INTERFACE (
            REQUEST_ID,
            TRANSACTION_TYPE,
            TRANSACTION_ID,
            PROCESS_FLAG,
            ITEM_NUMBER,
            ORGANIZATION_CODE,
            ALTERNATE_BOM_DESIGNATOR           )
        VALUES                                 (
            G_REQUEST_ID,
            G_TXN_CREATE,
            L_TRANSACTION_ID,
            1,
            L_PARENT_NAME,
            L_ORGANIZATION_CODE,
            L_STRUCTURE_NAME                   );

Line: 632

    Dbms_Sql.Close_Cursor(l_cursor_select);

Line: 635

    l_dyn_sql_insert := '';

Line: 636

    l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( REQUEST_ID, Transaction_Type, Transaction_Id, Process_Flag, ';

Line: 637

    l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, ';

Line: 638

    l_dyn_sql_select := '';

Line: 639

    l_dyn_sql_select := l_dyn_sql_select || 'SELECT REQUEST_ID, ''' || G_TXN_CREATE || ''', Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';

Line: 640

    l_dyn_sql_select := l_dyn_sql_select || 'DECODE(' || G_INTF_STRUCT_NAME || ',Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,' || G_INTF_STRUCT_NAME || '), ';

Line: 641

    l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ';

Line: 645

        l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';

Line: 649

          l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';

Line: 651

          l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';

Line: 657

    l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);

Line: 658

    l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);

Line: 659

    l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';

Line: 660

    l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';

Line: 661

    l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID ';

Line: 662

    l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ' || l_parent_column || ' IS NOT NULL ';

Line: 665

    l_dyn_sql_select := l_dyn_sql_select || ' AND (Transaction_Type = ''' || G_TXN_CREATE || ''' OR  Transaction_Type = ''' || G_TXN_ADD || ''' ';

Line: 666

    l_dyn_sql_select := l_dyn_sql_select || ' OR Transaction_Type = ''' || G_TXN_SYNC || ''' )';

Line: 669

    l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;

Line: 683

    l_dyn_sql_insert := '';

Line: 684

    l_dyn_sql_insert := l_dyn_sql_insert || 'INSERT INTO BOM_INVENTORY_COMPS_INTERFACE ( REQUEST_ID, Transaction_Type, Transaction_Id, Process_Flag, ';

Line: 685

    l_dyn_sql_insert := l_dyn_sql_insert || 'ORGANIZATION_CODE, ALTERNATE_BOM_DESIGNATOR, COMPONENT_SEQUENCE_ID, ';

Line: 686

    l_dyn_sql_insert := l_dyn_sql_insert || 'DELETE_GROUP_NAME, DG_DESCRIPTION, ';

Line: 687

    l_dyn_sql_select := '';

Line: 688

    l_dyn_sql_select := l_dyn_sql_select || 'SELECT REQUEST_ID, Transaction_Type, Transaction_Id, 1, ' || G_INTF_ORG_CODE || ', ';

Line: 689

    l_dyn_sql_select := l_dyn_sql_select || 'DECODE(' || G_INTF_STRUCT_NAME || ',Bom_Globals.Retrieve_Message(''BOM'', ''BOM_PRIMARY''),NULL,' || G_INTF_STRUCT_NAME || '), ';

Line: 690

    l_dyn_sql_select := l_dyn_sql_select || G_INTF_COMP_SEQ_ID || ', ';

Line: 691

    l_dyn_sql_select := l_dyn_sql_select || '''' || G_DEL_GROUP_NAME || ''', ''' || G_DEL_GROUP_DESC || ''', ' ;

Line: 695

        l_dyn_sql_insert := l_dyn_sql_insert || l_bom_col_name(i) || ',';

Line: 699

          l_dyn_sql_select := l_dyn_sql_select || 'TO_DATE(' || l_intf_col_name_tbl(i) || ',''DD-MON-YYYY HH24:MI:SS''),';

Line: 701

          l_dyn_sql_select := l_dyn_sql_select || l_intf_col_name_tbl(i) || ',';

Line: 707

    l_dyn_sql_insert := SUBSTR(l_dyn_sql_insert,0,LENGTH(l_dyn_sql_insert) - 1);

Line: 708

    l_dyn_sql_select := SUBSTR(l_dyn_sql_select,0,LENGTH(l_dyn_sql_select) - 1);

Line: 709

    l_dyn_sql_insert := l_dyn_sql_insert || ' ) ';

Line: 710

    l_dyn_sql_select := l_dyn_sql_select || ' FROM EGO_BULKLOAD_INTF ';

Line: 711

    l_dyn_sql_select := l_dyn_sql_select || ' WHERE RESULTFMT_USAGE_ID = :RESULTFMT_USAGE_ID ';

Line: 712

    l_dyn_sql_select := l_dyn_sql_select || ' AND PROCESS_STATUS = 1 AND ' || l_parent_column || ' IS NOT NULL ';

Line: 713

    l_dyn_sql_select := l_dyn_sql_select || ' AND Transaction_Type = ''' || G_TXN_DELETE || ''' ';

Line: 715

    l_dyn_sql := l_dyn_sql_insert || ' ' || l_dyn_sql_select;

Line: 725

      FND_FILE.PUT_LINE( FND_FILE.LOG,'Before UPdate of AssemblyType');

Line: 729

    UPDATE BOM_BILL_OF_MTLS_INTERFACE set assembly_type = 2
    where assembly_type is null;

Line: 733

      FND_FILE.PUT_LINE( FND_FILE.LOG,'After UPdate of AssemblyType');

Line: 743

      INSERT INTO BOM_BILL_OF_MTLS_INTERFACE
      (
        ASSEMBLY_ITEM_ID,
        ORGANIZATION_ID,
        ASSEMBLY_TYPE,
        PROCESS_FLAG,
        ORGANIZATION_CODE,
        COMMON_ORG_CODE,
        ITEM_NUMBER,
        IMPLEMENTATION_DATE,
        ALTERNATE_BOM_DESIGNATOR,
        TRANSACTION_TYPE,
        REQUEST_ID)
      VALUES
      (
        C_BOM_BILL_PRIMARY_REC.ASSEMBLY_ITEM_ID,
        C_BOM_BILL_PRIMARY_REC.ORGANIZATION_ID,
        C_BOM_BILL_PRIMARY_REC.ASSEMBLY_TYPE,
        C_BOM_BILL_PRIMARY_REC.PROCESS_FLAG,
        C_BOM_BILL_PRIMARY_REC.ORGANIZATION_CODE,
        C_BOM_BILL_PRIMARY_REC.COMMON_ORG_CODE,
        C_BOM_BILL_PRIMARY_REC.ITEM_NUMBER,
        C_BOM_BILL_PRIMARY_REC.IMPLEMENTATION_DATE,
        NULL,
        G_TXN_SYNC,
        G_REQUEST_ID);

Line: 772

    UPDATE BOM_BILL_OF_MTLS_INTERFACE
     SET  Transaction_Id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
    WHERE  REQUEST_ID = G_REQUEST_ID AND PROCESS_FLAG = 1
    and Transaction_Id is null;